1 / 18

Schema Conversion

Schema Conversion. Create a table for each entity 1:1 choose one side and put a foreign key 1:* put a foreign key in many side *:* create a table for the relation Relation *:* create a table . (1..1). (0..1). Staff( staffNo , name, position, salary, branchNo, Manager)

brooklyn
Download Presentation

Schema Conversion

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Schema Conversion • Create a table for each entity • 1:1 choose one side and put a foreign key • 1:* put a foreign key in many side • *:* create a table for the relation • Relation • *:* create a table

  2. (1..1) (0..1) Staff(staffNo, name, position, salary, branchNo, Manager) Branch(branchNo, street, city, postcode) Telephone (branchNo, telNo) OR Staff(staffNo, name, position, salary,branchNo) Branch(branchNo, street, city, postcode, staffNo) Telephone (branchNo, telNo)

  3. (1..*) (1..1) Staff(staffNo, name, position, salary, manages, belongs) Branch(branchNo, street, city, postcode) Telephone (branchNo, telNo) Domain [manages]= Domain[Branch.branchNo] Domain [belongs] = Domain[Branch.branchNo]

  4. staffNo BranchNo fName street lName manages city sex DOB postcode salary branchNo B005 22 Deer Rd London SW14EH SL21 John White B005 M Oct/1/45 30000 B005 B007 16 Argyll St. Aberdeen AB2 3SU SG37 Ann Beech F Nov/10/60 12000 B003 B003 163 Main St Glasgow G11 9QX SG14 David Ford M Mar/24/58 18000 B003 B004 32 Manse Rd Bristol BS99 1NZ SA9 Mary Howe F 19/Feb/70 9000 B007 B002 56 Clover Dr. London NW10 6EU SG5 Susan Brand B003 F Jun/3/40 24000 B003 SL41 Julie Lee F Jun/13/65 9000 B005 How a relationship works in tables. Staff (1..*) Branch (1..1) Find all the staff members who work in London.

  5. Many-to-Many Case PropertyForRent NewsPaper ◄Advertises newsPaperName {PK} propertyNo{PK} Street City Postcode Rooms Rent privateOwnerNo … (1..*) (0..*) dateAdvert Cost • PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, privateOwnerNo, • businessOwnerNo, staffNo, branchNo) • Newspaper(newspaperName) • Advertisement (newspaperName, PropertyNo, dateAdvert, cost)

  6. Exercise ◄Supervises Supervisor 0..1 Manages ► Employee SSN{pk} FName MINIT LName BDate Address Sex Salary Department Dnumber{pk} Dname Mgrstartdate Location[1..n] 1..1 1..0 ◄Has 1..* 1..1 1..* 1..1 In Charge of► Supervisee 1..* Works_on ► 1..1 ◄Depend On 0..* 0..* 0..* Project Pnumber{pk} Pname Plocation Dependent ESSN{pk} Dependent_Name{pk} Sex Bdate Relationship

  7. R1 R2 R3 R4 R5 R6 R={R1, R2, R3, R4, R5, R6}

  8. Exercise

  9. Representing Relational Database Schemas • DreamHome case study • Branch (branchNo, street, city, postcode, manager) • Staff (staffNo, fName, lName, position, sex, DOB salary, branchNo, supervisor) • PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, privateOwnerNo, businessOwnerNo, staffNo, branchNo) • Client (clientNo, fName, lName, telNo, prefType, maxRent) • Registration (clientNo, branchNo, staffNo, dateJoined) • Manager(staffNo, branchNo, MgrStartDate, bonus)

  10. Schema • Lease (leaseNo, PropertyNo, clientNo) • Preference (clientNo, preference) • Newspaper(newspaperName) • Advertisement (newspaperName, PropertyNo, dateAdvert, cost) • PrivateOwner (privateOwnerNo, fName, lName, address, telNo) • BusinessOwner (businessOwnerNo, businessName)

  11. Class Exercise Please complete the relational schema. Chairs► PROFESSOR DEPARTMENT 1..1 0..1 1..1 1..N IS ASSIGNED TO► PROFESSOR(PID, FName, MINIT, LName, DOB, Rank) DEPARTMENT (DID, DName)

  12. Exercise COURSE 1..1 Has▼ o..* ENROLL► SECTION STUDENTS 0..35 1..6 STUDENTS (SID, FName, LName, DOB, Major[1..3], Minor) COURSE (COURSE#, C_Name, Description) SECTION (Section_Num, Max_size)

  13. Exercise Paint ► (0..*) Displayed (1..1) ► Painter (1,1) (0..*) Painting Gallery Painter (Painter_ID, FName, LName) Paining (Painting_ID, Title, Date) Gallery (Gallery_Name, Location)

  14. Exercise Learn ► 0..* 0..* Employee Skills Expert Level Employee (EID, FName, LName) Skill (Skill_Name)

  15. Exercise Take ► 1..* 0..* Student Classes Grade STUDENTS (SID, FName, LName, DOB) CLASS (CLASS#, C_Name, Description)

  16. Exercise (Recursive and many-to-many) STUDENTS (SID, FName, LName, DOB) COURSE (COURSE#, C_Name, Description) SECTION (Section_Num, Max_size) Has ► 1..1 0..* Course Section 0..* 0..* 0..* Take ▼ Grade ◄Prerequisite 1..* Student

  17. Exercise Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course: STUDENT(SSN, Name, Major, Bdate) COURSE(Course#, Cname, Dept) ENROLL(SSN, Course#, Quarter, Grade) BOOK_ADOPTION(Course#, Quarter, Book_ISBN) TEXT(Book_ISBN, Book_Title, Publisher, Author) Quarter(Quarter) Draw a relational schema diagram specifying the foreign keys for this schema.

  18. Course# Cname Dept Grade Student SSN{pk} Name Major Bdate Course Enroll Adopt Quarter Quarter{pk} Text ISBN{pk} Publisher Author

More Related