数据库管理系统概述英文版课件：tutorial2 Relational Data Model Relational Algebra

1、COMP231 Tutorial 2Relational Data ModelRelational AlgebraE-R diagram for a university.titledeptnameiidprerequisitemaincoursecourse_norequirescoursesyllabuscreditstimeroomsection_nosemesteryearcourse- offeringsis offeredenrolsgradenamesidprogramstudentinstructorteachestitleExercise: Convert E-R Diagr

2、am into tablesE-R diagram for a university.titledeptnameiidprerequisitemaincoursecourse_norequirescoursesyllabuscreditstimeroomsection_nosemesteryearcourse- offeringsis offeredenrolsgradenamesidprogramstudentinstructorteachestitleEntities (Not Weak)Tables of Entities (Not Weak)student (sid, name, pr

3、ogram)course (course_no, title, syllabus, credits)instructor (iid, name, dept, title)titledeptnameiidprerequisitemaincoursecourse_norequirescoursesyllabuscreditstimeroomsection_nosemesteryearcourse- offeringsis offeredenrolsgradenamesidprogramstudentinstructorteachestitleE-R diagram for a university

4、.titledeptnameiidprerequisitemaincoursecourse_norequirescoursesyllabuscreditstimeroomsection_nosemesteryearcourse- offeringsis offeredenrolsgradenamesidprogramstudentinstructorteachestitleWeak EntityTable of Weak Entitycourse-offerings (course_no, section_no, year, semester, time, room)titledeptname

5、iidprerequisitemaincoursecourse_norequirescoursesyllabuscreditstimeroomsection_nosemesteryearcourse- offeringsis offeredenrolsgradenamesidprogramstudentinstructorteachestitleE-R diagram for a university.titledeptnameiidprerequisitemaincoursecourse_norequirescoursesyllabuscreditstimeroomsection_nosem

6、esteryearcourse- offeringsis offeredenrolsgradenamesidprogramstudentinstructorteachestitleRelationships (without weak entities)Tables of Relationships (without weak entities)enrols (sid, course_no, section_no, semester, year, grade)teaches (iid, course_no, section_no, semester, year)requires (main_c

7、ourse_no, prerequisite_no)titledeptnameiidprerequisitemaincoursecourse_norequirescoursesyllabuscreditstimeroomsection_nosemesteryearcourse- offeringsis offeredenrolsgradenamesidprogramstudentinstructorteachestitleE-R diagram for a university.titledeptnameiidprerequisitemaincoursecourse_norequirescou

8、rsesyllabuscreditstimeroomsection_nosemesteryearcourse- offeringsis offeredenrolsgradenamesidprogramstudentinstructorteachestitleRelationship with Weak EntityTable of Relationship with Weak EntityThere is no extra table for the relationship between a weak entity and its “owner/strong” entity. The re

9、lationship is already present in the table for the weak entity. course-offerings (courseno, secno, year, semester, time, room)If the relationship between A and B is many-to-oneInclude the key of B into the table of AIf the relationship between A and B is one-to-oneInclude either A or Bs key into the

10、 others tableKeySuperkey Uniquely identify the tuples in the relationCandidate Key (also called key) A minimal superkey (cannot remove any attribute to make it as a superkey)Primary Key A candidate key used in the relation to identify tuplesRelational Algebra - operationsGiven the following tables:

11、employee (person_name, street, city)works (person_name, company_name, salary)company (company_name, city)manages (person_name, manager_name)Select: Find all people who work in “First Bank Corporation”Project: Find all person names that have a job to work.Union: Find every person who is either an emp

12、loyee or a manager (there is automatic duplicate elimination in all algebra operations)Intersection: Find all persons that are both employee and manager.Set Difference: Find all persons that are employee but not manager.Rename: Rename the table employee to employee2Cartesian Product: Produces all po

13、ssible combinations of records from employee and works.Natural Join: Show the employee names, city, and the salary for their jobs (for employees who work in some company).Division: Find all persons that work in all companies.Relational Algebra - operationsemployee (person_name, street, city)works (p

14、erson_name, company_name, salary)company (company_name, city)manages (person_name, manager_name)Select: Find all people who work in “First Bank Corporation”company_name=“First Bank Corporation”(works)Projection: Find all person names that have work.person_name(works)Union: Find every person who is e

15、ither an employee or a manager (there is automatic duplicate elimination in all algebra operations)person_name(employee)manager_name(manages)Intersection: Find all persons that are both employee and manager.person_name(employee)manager_name(manages)Relational Algebra - operationsemployee (person_nam

16、e, street, city)works (person_name, company_name, salary)company (company_name, city)manages (person_name, manager_name)Set difference: Find all persons that are employee but NOT manager.person_name(employee) manager_name(manages)Rename: Rename the table employee to employee2(employee2, employee)Car

17、tesian Product: Produces all possible combinations of records from employee and works.employee worksNatural Join: Show the employee names, city, and the salary for their jobs (for employees who work in some company).person_name, city, salary(employee works)Relational Algebra - operationsemployee (pe

18、rson_name, street, city)works (person_name, company_name, salary)company (company_name, city)manages (person_naame, manager_name)Division: Find all persons that work in ALL companies.(person_name, company_name(works) / (company_name(company)Relational Algebra ExerciseGiven the following tables: empl

19、oyee (person_name, street, city)works (person_name, company_name, salary)company (company_name, city)manages (person_name, manager_name)Find the names of all persons who work for “First Bank Corporation”Find the names and cities of employees who work for “First Bank Corporation”Find the name of all

20、managers who earn more than $10,000Find the worker(s) with highest salary Find the names of employees who do not work for “First Bank Corporation”Find the employees that hold a job in all companies located in Hong KongFind the names of employees who live in the same city and on the same street as th

21、eir managersRelational Algebra Exerciseemployee (person_name, street, city)works (person_name, company_name, salary)company (company_name, city)manages (person_naame, manager_name)Find the names of all persons who work for “First Bank Corporation”person_name (company_name = “First Bank Corporation”

22、(works)Relational Algebra Exerciseemployee (person_name, street, city)works (person_name, company_name, salary)company (company_name, city)manages (person_name, manager_name)Find the names and cities of employees who work for “First Bank Corporation”person_name, city (employee (company_name = “First

23、 Bank Corporation” (works) Relational Algebra Exerciseemployee (person_name, street, city)works (person_name, company_name, salary)company (company_name, city)manages (person_name, manager_name)Find the name of all managers who earn more than $10,000person_name (salary “10000” (works) manager_name (

24、manages)Relational Algebra Exerciseemployee (person_name, street, city)works (person_name, company_name, salary)company (company_name, city)manages (person_name, manager_name)Find the worker(s) with highest salary person_name (works) - w1.person_name ( w1.salaryw2.salary (w1, works) (w2, works)Relational Algebra Exerciseemployee (person_name, street, city)works (person_name, company_name, salary)company (company_name, city)manages (person_name, manager_name)Find the names of employees who do not work for “First Bank Corporation”person_name(employee) -person_name( com