1. Identify the foreign keys in this Schemal. Explain how the entity and referential integrity rules apply to these relations. Hotel (hotelNo, hotelName, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo. dateFrom, date To, roomNo) Guest (guestNo, guestName, guestAddress) Schema. 1 2. Students can lease university flats and some of the details of leases held by students for places in university flats are shown in Fig 1. Describe and illustrate the process of normalizing the attributes shown in Fig 1 to produce a set of well-designed 3NF. relations. (Note: A place number uniquely identifies each single room in all flats and is used when leasing a room to a student). lease No bannerlD placeNo fName IName startDate 01/09/2010 01/09/2011 01/09/2011 01/09/2011 01/09/2012 01/09/2012 10003 10259 10364 10566 11067 11169 B017706 B017706 B013399 B012124 B034511 B013399 78 88 89 102 88 78 Bed Number: 84 Drug Number Jane Jane Tom Karen Steven Tom Full Name: Robert MacDonald 120 Lady Lane, Paisley 111 Storrie Road, Paisley 34 High Street, Paisley Fig 1: Table displaying sample data for university accommodation. Watt Watt Jones Black Smith Jones 3. Describe the three functional components of the DBMS environment. b. Discuss how they relate to each other? 4. a. Discuss the differences between the candidate keys and the primary key of a relation. b. Explain what is meant by a foreign key. c. How do foreign keys of relations relate to candidate keys? Give examples to illustrate your answer. 5. Examine the Patient Medication data shown in Figure 1 a. Identify the functional dependencies represented by the attributes. State any assumptions that you make about the data and the attributes shown in this figure. b. Describe and illustrate the process of normalizing the attributes shown in figure to produce a set of well- designed 3NF relations. Wellmeadows Hospital Patient Medication Form Patient Number: P10034 Name Description Dosage 10223 Morphine Pain Killer 10mg/ml 10334 Tetracyclene Antibiotic 0.5mg/ml 10223 Morphine Pain Killer 10mg/ml finishDate 30/06/2011 30/06/2012 30/06/2012 30/06/2012 30/06/2013 30/06/2013 Ward Number: Ward 11 Ward Name: Orthopaedic Oral M Oral flatNo F56 F78 F78 F79 F78 F56 Method of Units per Start Date Finish Date Admin Day 50 10 10 flatAddress 34 High Street, Paisley 111 Storrie Road, Paisley 111 Storrie Road, Paisley 24/03/13 24/04/14 17/04/13 24/03/13 25/04/14 02/05/15

1. Identify the foreign keys in this Schemal. Explain how the entity and referential integrity rules apply to these relations. Hotel Cotelia, notelume oty) Schema. I 2. Students can lease university flats and some of the details of leases held by students for places in university flats are shown in Fig 1. Describe and illustrate the process of normalizing the attributes shown in Fig 1 to produce a set of well-designed 3NF, relations. (Note: A place number uniquely identifies each single room in all flats and is used when leasing a room to a student). Fig 1: Table displaying sample data for university accommodation. 3. Describe the three functional components of the DBMS environment. b. Discuss how they relate to each other? 4. a. Discuss the differences between the candidate keys and the primary key of a relation. b. Explain what is meant by a foreign key. c. How do foreign keys of relations relate to candidate keys? Give examples to illustrate your answer. 5. Examine the Patient Medication data shown in Figure 1 a. Identify the functional dependencies represented by the attributes. State any assumptions that you make about the data and the attributes shown in this figure. b. Describe and illustrate the process of normalizing the attributes shown in figure to produce a set of welldesigned 3NF relations