1. Database modelling

(a) Draw an E-R diagram to model the following scenario. Show clearly any cardinality or participation constraints for relationships in your diagram. State any assumptions you need to make. 

(b) Derive a relational schema for your E-R diagram from part (a). Be sure to specify sensible attribute types, and any necessary primary key, foreign key, and non-null constraints. 

(c) Give SQL queries over SQL tables corresponding to your relational schema from part (b) for the following queries (minor syntax errors will not be penalized, provided it is clear what you meant)

(i) List names and funding bodies of all research projects, ordered alphabetically by the funding body and then by name. 

(ii) List names and emails of all staff members having part-time employment in the School of Chemistry, ordered by the percentage of the time they employed for. 

(iii) For each task of the EU-funded project called "Advance", list the task number and its short description, the total number of part-time staff members who worked on this task in June 2022, and the total number of days they worked on this task in June 2022. 

2. SQL

(a) Write suitable SQL DDL statements to create the tables as above. Include attribute types, primary and foreign key constraints.

(b) Write SQL DML queries over the tables defined in part (a) to list

(i) Names of magazines and their unit price for greyscale advertisements, ordered by price, most expensive first.

(ii) Names of customers placing advertisements in the category "Food" in May 2022, listed in alphabetical order by customer name. 

(iii) Names of categories and the total number of advertisements placed in each category in April 2022, in descending order by the number of advertisements. 

(c) Imagine that user requirements have been changed to allow multiple copies of the same advertisement in the same issue of a magazine. Write SQL statement(s) to modify the placement table that has been already defined in part (a), by adding the new attribute quantity and setting its value to 1 for all placements contained in this table. 

(d) Give two reasons to define your own SQL functions. Explain how functions may be useful for calculating the cost of a particular placement of an advertisement from the placement table. You do not have to write SQL code, but may name relevant SQL commands while giving an outline of your solution. (Advisory word limit of the answer for 2(d) is 200 words.)

3. Normalization

(a) Explain why this data is unnormalized. Give two reasons why leaving it in this form is not useful, and convert the data to 1NF. (Advisory word limit of the answer for 3(a) is 200 words.) 

(b) Name three types of update anomalies. Demonstrate how your answer to part (a) may be vulnerable to them, by providing three examples of update anomalies (one for each type). If necessary, you may add more rows to the table to demonstrate this. 

(c) Briefly explain what is meant by a functional dependency. Identify the minimal set of functional dependencies that hold on your answer table from part (a). 

(d) Convert the data from part (a) to 2NF, and then to 3NF, making clear each step.

(e) After creating a database corresponding to your answer in 3NF from part (d), how would you use views and authorization to ensure that pupils can read school's timetable but will be unable to see teachers' staff IDs and classrooms' phone numbers and maximal capacity? (Advisory word limit of the answer for 3(e) is 200 words.) 

咨询 Alpha 小助手，获取更多课业帮助。