Database system concept

Database system concept

introduction

Data view

  1. Physical layer: the lowest level of abstraction that describes how data is actually stored.
  2. Logical layer: describe what data is stored in the database and what relationship exists between these data.
  3. View layer: describes a part of the data database.
Examples and patterns

  1. 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.

  2. 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

  3. Secondary mapping

  • 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 model

Data + data relation + data semantics + consistency constraint
a) Relational model
b) Entity relation model
c) Object based data model
d) Semi structured data model

Database language

  1. Data manipulation language (DML)
    a) Processization
    b) Declarative
  2. Data definition language (DDL)
    a) Domain constraints
    b) Referential integrity
    c) Assert
    d) Authorization

Database design

Requirement analysis - Conceptual Design - logical design - Physical Design

SQL

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.

e.g.

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);

4. insert

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;

5. update

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;

5. select

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.

  • sort
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.
11. Clustering
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

nested subqueries

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);

Tags: Database SQL

Posted by jkkenzie on Mon, 18 Apr 2022 22:46:40 +0930

Popular Tags

©2023 Programs Wiki