Samples - aTime staffNo dentistName PK 3NF BCNF fd1 fd2...

Info icon This preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
11.11 You are required to create a conceptual data model of the data requirements for a company that specializes in IT training. The Company has 30 instructors and can handle up to 100 trainees per training session. The Company offers five advanced technology courses, each of which is taught by a teaching team of two or more instructors. Each instructor is assigned to a maximum of two teaching teams or may be assigned to do research. Each trainee undertakes one advanced technology course per training session. (a) Identify the main entity types for the company. (b) Identify the main relationship types and specify the multiplicity for each relationship. State any assumptions you make about the data. (c) Using your answers for (a) and (b), draw a single ER diagram to represent the data requirements for the company. Research Instructor 0..* Assigned 1..1 Advanced Course 1..1 Teaches 1..* Training Session 1..* Provides 1..1 TeachingTe am 1..1 IsPartOf 0..2 Trainee 1..* Attends 1..*
Image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
staffNo aDate
Image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: aTime staffNo dentistName PK 3NF / BCNF fd1 fd2 staffNo aDate fd4 FK FK PK patNo surgeryNo fd5 FK PK staffNo aDate aTime dentistName patNo patName PK fd2 1NF surgeryNo fd4 fd2 and fd4 violates 2NF 2NF staffNo aDate Fd3’ violates 3NF surgeryNo staffNo dentistName fd1 fd3 staffN aDate aTime patNo patName fd5 patNo patName PK Fd3’ For the Exercises 5.7 – 5.28, use the Hotel schema defined at the start of the Exercises at the end of Chapter 3 (p 87). Simple Queries 5.7 List full details of all hotels. SELECT * FROM Hotel ; 5.11 List the bookings for which no dateTo has been specified. SELECT * FROM Booking WHERE dateTo IS NULL; 5.21 What is the lost income from unoccupied rooms at the Grosvenor Hotel? SELECT SUM(price) FROM Room r WHERE roomNo NOT IN (SELECT roomNo FROM Booking b, Hotel h WHERE ( dateFrom <= CURRENT_DATE AND dateTo >= CURRENT_DATE) AND b.hotelNo = h.hotelNo AND hotelName = ‘Grosvenor Hotel’);...
View Full Document

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern