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

Info iconThis 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..*
Background image of page 1

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

View Full DocumentRight Arrow Icon
staffNo aDate
Background image of page 2
Background 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

This note was uploaded on 05/13/2010 for the course COMPRO CS401 taught by Professor Just during the Spring '10 term at Maharishi.

Page1 / 3

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

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online