Database system concept
- Physical layer: the lowest level of abstraction that describes how data is actually stored.
- Logical layer: describe what data is stored in the database and what relationship exists between these data.
- View layer: describes a part of the data database.
Examples and patterns
Example: a collection of information stored in the database at a specific time;
a) The specific value of the mode;
b) Reflect the status of the database at a certain time;
c) the first mock exam can be used in the same mode.
d) The instance changes as the data is updated.
Mode: the overall design of the database, the description of the logical structure and characteristics of the database; Is a description of the type; Reflect the structure and connection of data; The model is relatively stable;
a) Physical mode: storage mode in physical layer
b) Logical mode: global mode conceptual mode of logical layer
c) Sub mode: view layer external mode user mode local mode
Independence of physical data: independent of physical model.
A database has only one internal schema
- External mode / mode mapping: the mode changes and the external mode remains unchanged
- Mode / internal mode mapping: the internal mode changes and the mode remains unchanged
Data + data relation + data semantics + consistency constraint
a) Relational model
b) Entity relation model
c) Object based data model
d) Semi structured data model
- Data manipulation language (DML)
- Data definition language (DDL)
a) Domain constraints
b) Referential integrity
Requirement analysis - Conceptual Design - logical design - Physical Design
1. Create relationships
create table section( course_id varchar(20), sec_id varchar(9) not null, year numeric(4,0), semester varchar(6), building varchar(16), room_number varchar(11), primary key (course_id, sec_id ,semester,year), foreign key(course_id) references courses(course_id) );
Primary keys can also be written together with consolidated attributes
create table course( course_id varchar(20) primary key, title varchar(20), dept_name varchar(10), credits numeric(2,0), foreign key (dept_name) references department(dept_name) );
2. alter table
Add a property for the existing relationship, and the value of the new property is null.
alter table r add A D
The relationship is r, A is the name of the added attribute, and D is the domain of the added attribute.
alter table instructor add age int;
The removed attribute is:
alter table r drop A; alter table instructor drop age;
3. delete and drop
Delete is used to delete Yuanzu from the relationship
delete from student
Delete all tuples in the student relationship, but keep the relationship;
drop table r, delete r directly. After deletion, you can only use create table to rebuild.
Delete teachers working in the Department in the Waston building from the instructor.
delete from instructor where dept_name in (select dept_name from department where building='Waston');
Delete teachers whose salary is lower than the average salary of the University
delete from instructor where salary <( select avg(salary) from instructor);
insert into department values('Biology','Warson','90000'); insert into instructor values ('22',null, 'Physics',9000);
I want every student who has completed 144 credits in the Music department to become a teacher in the Music department, and the salary is 18000
insert into instructor select ID,name,dept_name,18000 from student where dept_name="Music" and tot_cred>144;
update instructor set salary=salary*1.05 where salary<(select avg(salary)from instructor)
update has a case structure
update instructor set salary = case when salary<=10000 then salary*1.05 else salary*1.03 end;
select dept_name from instructor;
- Here, dept_name will repeat. The method to remove the duplicate is to use distinct.
select distinct dept_name from instructor;
- select can have operators
select salary*1.1 from instructor;
- select uses where for tuples that satisfy a particular predicate
select name from instructor where depart_name='Sci' and salary >5000;
between means that a value is in the middle
select name from instructor where salary between 9000 and 10000;
SQL allow ()
select name from instructor,teachers where (instructor.ID,dept_name)=(teacher.ID,'Biology');
- *Represents all attributes
select instructor.* from instructor,teachers where instructor.ID=teacher.ID;
Indicates that all attributes in the instructor are selected, and select * indicates that all attributes of the result relationship of the from clause are selected.
select * from instructor order by salary desc, name asc;
order by enables the query results to be displayed in order. The default is ascending asc and desc descending.
6. natural join
For all the teachers who give lectures in the University, find out their names and the identification of all the courses they talk about.
select name, course_id from instructor, teachers where instructor.ID=teacher.ID;
The above is the combination of instructor and teachers through Cartesian product, which can be changed into
select name, course_id from instructor natural join teachers;
Natural connection will find those with the same value of common attribute ID and those with the same name.
To avoid unnecessary equality attributes
select name,title from (instructor natural join teachers)join course using course_id;
7. Name change
select name,course_id from instructor,teachers where instructor.ID = teachers.ID;
as can be used in select or from. The above formula can be changed to:
select name as instructor_name, course_id from instructor,teachers where instructor.ID = teachers.ID; select name, course_id from instructor as T,teachers as S where T.ID = S.ID;
When it is necessary to compare tuples in the same relationship.
select distinct T.name from instructor as T, instructor as S where T.salary >S.salary;
8. String operation
- Convert upper to uppercase; Convert lower to lowercase
- %Match any substring
- _ Match any character
Find out all the Department names that contain the substring Watson in the name of the building
select dept_name from department where building like "%Watson%";
- Use escape to define escape characters, such as matching all strings starting with ab%cd:
like 'ab\%cd%' escape '\'
- not like is used to search for mismatches
9. Set operation
- union parallel operation, automatic removal of duplicate
(select course_id from section where semester = 'Fall'and year =2009) union select course_id from section where semester = 'Spring'and year =2010) );
Find out all courses that start in the fall of 2009 or in 2010 or both semesters.
If you want to keep all duplicates, use union all instead of union.
- intersect operation to automatically remove duplicate
(select course_id from section where semester = 'Fall'and year =2009) intersect select course_id from section where semester = 'Spring'and year =2010) );
All courses that start in two semesters.
If you want to keep all duplicates, use intersect all instead of intersect.
- The difference operation except automatically removes the duplicate
(select course_id from section where semester = 'Fall'and year =2009) except select course_id from section where semester = 'Spring'and year =2010) );
Fall 2009 - Spring 2010.
If you want to keep all duplicates, use except all instead of except.
10. Null value
select name from instructor where salary is null;
Pay attention to the relationship with unknown.
avg min max sum count
Find out the average salary of teachers in CS department
select avg(salary) as avg_salary from instructor where dept_name='CS';
The number of teachers who taught a course in the spring of 2010. distinct means to remove duplication, and count is used to calculate the number of tuples in a relationship.
select count(distinct ID) from teachers where semester = 'Spring' and year = 2000;
Note: distinct is not allowed for count(*).
- Grouping and clustering: group by is used to give one or more attributes and construct grouping.
select dept_name, avg(salary) from department group by dept_name;
Find out the average salary of each department.
Find out the number of teachers teaching courses in each department in the spring of 2010.
select dept_name ,count(distinct ID)as inst_count from instructor natural join teachers where semester = 'Spring' and year = 2010 group by dept_name;
Note: any attribute that does not appear in group by can only appear inside the clustering function, otherwise it is wrong. For example, the above ID is only in count. If it is listed separately, each teacher has an ID, and each group only outputs one ID, which cannot be selected.
- having works after grouping is formed, indicating the grouping conditions after grouping
select dept_name, avg(salary) as avg_salary from instrutor group by dept_name having avg(salary) > 40000
Order: from -- where -- group by -- having -- select
Courses starting in the fall of 2009 and in 2010.
- Use not in
select course_id from section where semester = 'Spring' and year = 2010 and course_id in (select course_id from section where semester = 'Fall' and year = 2009 and );
select distint name from instructor where name not in ("Sherry","Smith");
some means bigger than at least one
select name from instructor where salary >some(select salary from instructor where dept_name="Biology");
Note: = some is equivalent to in; < > All is equivalent to not in; Therefore, = all is not equivalent to in, and < > some is not equal to not in.
- Subquery in from
select max(tot_salary) from ( select dept_name,sum(salary) from instructor group by dept_name)as dept_total(dept_name,tot_salary);