Code ER Diagrams R Programming & Database Management Exercises

UWC MySQL Code ER Diagrams R Programming & Database Management Exercises

IB9HP0 University of Warwick

Part A (20 Marks) The following logical schema is provided (primary keys underlined, foreign keys in italics):

TB1(col111, col112, col113, col114) TB2(col211, col111, col222, col223, col224, col225) TB3(col311, col312, col313, col314)

a. Provide the ER diagram (entities, attributes, relationships as well as the cardinality of the relationships) based on the definition of primary and foreign keys provided on the logical schema. (10 Marks)

b. You are given the information that columns col311 and col111 are the same. How does your ER diagram look after that? (5 Marks)

c. You are given the information that col223 is multivalued and contains at most three values (v1, v2, v3) separated by comma. How does the logical schema look after that information? (5 Marks)

Part B (15 Marks) An airline flight is recognised by a unique flight number and involves registering the departure airport, the destination airport, the make of the aircraft as well as the date that the flight is scheduled. A passenger is booked to a flight using a unique booking reference. Each passenger is identified by a passenger code, first name, last name, gender and food preference. Each booking contains information about the date of the booking, the seat class, the price of the booking as well as the channel through which the booking was made. Each channel is identified by a unique code, the contact person details (first name, last name), and carries its own (fixed) fee percentage. The channel facilitates the booking.

a. Provide the E-R diagram depicting the entities and the relationships provided in the above scenario. Using relationship sets, explain how you determined the cardinality of these relations. (10 Marks)

b. Transfer the E-R diagram to a logical design. You need to explain how you transferred each relationship to the logical design. (5 Marks)

Part C (10 Marks) The following table (TBL1) is given. cola is the primary key (highlighted in bold).

cola 1231 1232 1233 1234 1235 1236 … colb A B C D E E … colc 40 50 34 32 43 42 …. cold 50 51 50 51 52 53 … Provide the following SQL statements

a. Calculate the average for columns colc and the sum for cold (2 Marks)

 b. Calculate the total number of records for each instance of the colb (2 Marks)

c. How many unique records of colb exist? (1 Mark)

d. How many records exist on the table where colb is either A or E and colc is less than or equal to 100 and cold is greater than colc (5 Marks)

Part D (25 Marks) The following table (employees) is given:

employeeID 0001 name John Smith 0002 Maria Verena Jones 0003 Jason Cleeland 0004 Maria Josepha Palangos 0005 Veronica Chloe Jones address 23 Rampant Street, NR5 2FD, Norwich 28 Duke Street, CV5 2FD, Coventry 14 Farm House, CV3 2NG, Coventry 23 St Stephens Street, NR1 3SA, Norwich NULL salary 23,000 USD, plus 4,000 pension 14,000 USD job_cover frontdesk, backoffice, auxiliary frontdesk 42,000 EUR backoffice 5,000 USD plus 1,332 pension frontdesk 22,000 USD plus auxiliary 1,450 pension Where name, address and salary are composite attributes and functional dependencies of employeeID.

 a. Normalize the above table to another table (or tables) that correspond to the highest possible normal form. Justify your answer, using functional dependencies. (15 Marks)

b. Provide the logical schema of the new table or tables. (5 Marks)

c. Answer the following questions using the corresponding SQL statements:

i. How many employees are stationed in Coventry? (2 Marks)

 ii. What is the total salary cost (including pension) that the company paid in USD? (2 Marks) iii. What is the total salary cost (excluding pension) that the company paid per job_cover function? (1 Mark)

Part E (15 Marks) The following table (TABLE) is provided: col111 A A B B C D col112 peek show peek show peek show col113 123 111 111 112 10 11

a. Is TABLE normalized? if so, in which normal form? Does it need to be transformed? Explain your answer using functional dependencies. (5 Marks)

 b. You are given the information that the values peek and show are attributes of the TABLE. Name and describe the operation that you will perform and what the new table will look like. (5 Marks)

c. Provide the SQL statement to calculate the difference between peek and show. Can this query be executed? If so, explain why. Is there an alternative way without transformation? (5 Marks)

Part F (15 Marks) The following dplyr pipe sequence is given: 1 2 3 4 5 6 7

a. dataset1 %>% select(col1,col2,col3) %>% filter(col1>3) %>% group_by(col1) %>% summarise(totaln = sum(col2)) %>% left_join(dataset2) %>% select(va3, totaln) Provide the logical design equivalence from the dplyr pipe sequence. (5 Marks)

b. Using the above pipe sequence provide the SQL equivalent for up to line 3. (5 Marks)

c. Provide the SQL equivalent of the complete pipe sequence (line 1 to line 7). (5 Marks)

Collepals.com Plagiarism Free Papers

Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.

Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS

Why Hire Collepals.com writers to do your paper?

Quality- We are experienced and have access to ample research materials.

We write plagiarism Free Content

Confidential- We never share or sell your personal information to third parties.

Support-Chat with us today! We are always waiting to answer all your questions.