Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Computer Science

4COM2004

Data Modelling for Databases

2002/23

Semester A

QUESTION 1 – Entity-Relationship Modelling 1 (20 Marks)

The Hertfordshire Restaurant Guide website has an underlying database which provides upto date reviews of restaurants in Hertfordshire. Details about the restaurant are recorded, such as, its location, capacity, opening hours and the price range of its menu using a scale 1-4 (4 being expensive). The types of cuisines that restaurants can serve is recorded. The website has several reviewers, with various levels of experience, working for it. They visit restaurants regularly and may visit the same restaurant more than once, but never on the same day. These reviewers rank the restaurant against a few criteria on each visit. These criteria are:

· Quality of food

· Timeliness of service

· Cleanliness of restaurant

· Value for Money

Using the specification above, answer the following questions:

a) List 4 entities identified in the specification describing the objects they contain [8 marks]

b) Describe any two relationships defining for both, the:

i. Name (in one direction)

ii. Cardinality

iii. Participation

ensure you detail the assumptions you make in each case [8 marks]

c) Identify one of the following within any of the entities identified:

i. A possible simple key with explanation

ii. A possible composite key with explanation [4 marks] 

Sample Solution – Other entities may be accepted if you consider valid

 

QUESTION 2 – Entity-Relationship Modelling 2 (20 Marks)

The Hatfield Apple Tree Suppliers (HATS) grow apple trees for supply to the public via an on-line ordering system. HATS produce many different varieties of trees for which there are details such as the variety of tree by name, when it flowers and when the fruit is available, plus, the size and colour of the fruit. Individual trees are grown of differing varieties and HATS maintains a stock of trees of differing ages and sizes of all varieties.

Customers details are recorded including contact details ie, address and phone number and personal details, i.e. DoB. Customers can place orders for individual trees from the list of trees in stock, each order potentially contains a set of order items, each order item details a single tree ordered.

Using the specification above, answer the following questions:

a) List 4 entities identified in the specification describing the objects they contain [8 marks]

b) Describe any two relationships defining for both, the:

i. Name (in one direction)

ii. Cardinality

iii. Participation

ensure you detail the assumptions you make in each case [8 marks]

c) Identify one of the following within any of the entities identified:

i. A possible simple key with explanation

ii. A possible composite key with explanation [4 marks] 

Sample Solution – Other entities may be accepted if you consider valid

QUESTION 3 – Normalisation (30 Marks)

a) Badgeston Stadium supports a number of cricket events throughout the summer, customers can purchase tickets for these events via an on-line ordering system. An invoice is emailed to the customer reflecting each order which can contain ordered for a number of tickets to a number of events. Here is an invoice example.

 

Using the details and invoice example above, the Un-normalised form (UNF) has been extracted as follows:

( Customer_ID, Customer_Name, Customer Address, ( Event, Event_Date, ( Stand, Row, Seat, Unit_Price )))

Identify and write out the First Normal Form (1NF) in relational schema form

( Customer_ID, Customer_Name, Customer Address, Event, Event_Date, Stand, Row, Seat, Unit_Price )

or

( Customer_ID, Customer_Name, Customer Address, Event, Event_Date, Ticket_No, Stand, Row, Seat, Unit_Price )

b) A car showroom employs a number of sales staff, who are able to sell any car to any customer, in fact a sale may be undertaken by more than one sales staff, in which case the sales commission is shared. A car is sold to a single keeper (customer) and the sales people earn commission for each sale as a proportion of the agreed price of sale, which may be less than the recommended retail price. The commission proportion varies for each car sold.

The following First Normal Form (1NF) has been derived

(Salesperson_ID, Salesperson_Name, Date_Sold, Car_ID, Car_Make, Car_Model, Car_Colour, Car_Retail_Price, Car Sale_Price, Customer_ID, Customer Name, Customer_Phone_Number, Commission)

Identify and write out the Second Normal Form (2NF) in relational schema form

(Salesperson_ID, Salesperson_Name)

(Car_ID, Car_Make, Car_Model, Car_Colour, Date_Sold, Car Sale_Price, Car_Retail_Price)

(Salesperson_ID*, Car_ID*, Customer_ID, Customer Name, Customer_Phone_Number, Commission)

c) A logical model for a Lending Library has been developed. The database records which books have been loaned, creating a record for each loan and uniquely identifying it. The loan record also identifies, when, which book and by whom the loan was made for, with dates of the loan starting and how long the loan is for.

The First Normal Form (1NF) is as follows:

Loan ( Book_ID, ISBN, Title_of_Book, Member_ID, Member_Name, Member Address, Member_Phone_No, Member_Join_Date, Loan_ID, Loan_Date, Length_of Loan )

If we know the Loan_ID, we can determine the Book and the Member that loaned it, so as this is a Simple Primary Key, it is also in 2NF.

Identify and write out the Third Normal Form (3NF) in relational schema form

Loan (Loan_ID, Book_ID*, Member_ID*, Loan_Date, Length_of Loan )

Book ( Book_ID, ISBN*)

Title (ISBN, Book_Title)

Member (Member_Name, Member Address, Member_Phone_No, Member_Join_Date)

d) A scientist is working to determine different sugar content of various fruits. They record each experiment which involves taking a sample of a particular fruit and how much the sample weighs, then recording the findings of the experiment, which determines (by weight) how much of various types of sugars (fructose, sucrose, maltose, etc ) the sample contains. Each record indicates the content of a specific sugar in a specific sample experiment.

The UNF is derived as follows:

(Experiment_No, Date of Experiment, Fruit_Type, Sample_Weight, (Sugar_Type, Sugar_Description, Weight_in Sample))

Identify and write out the First Normal Form (1NF) in relational schema form.

(Experiment_No, Date of Experiment, Fruit_Type, Sample_Weight, Sugar_Type, Sugar_Description, Weight_in Sample)

e) Richard Osman's House of Games TV quiz show follows a particular format. There is a new competition each week with 4 competitors. Over 5 days (Monday to Friday), they play a number of games for points. On each day there are 5 games played in sequence. The order these games are played in is random, except for the last game each day (the fifth game each day) which is always the same (AnswerSmash). For the purposes of the database, we can record the name of these separate games as AnswerSmash1, AnswerSmash2, ... AnswerSmash5. The score of each individual game by each individual competitor needs to be recorded. Occasionally, a competitor may return for a second time to compete in a special competition, "Champion of Champions". The First Normal Form (1NF) has been identified as below:

Game_Score ( Competition_No, Start_Date, Competitor_No, Competitor_Name, Competitor_Occupation, Game_Name, Day_Game_Played, Sequence_No_of_Game_on_Day, Score )

Identify, and write out in Relational Schema form, the Second Normal Form (2NF).

Game ( Competition_No, Game_Name, Day_Game_Played, Sequence_No_of_Game_on_Day)

Competition ( Competition_No, Start_Date)

Competitor (Competitor_No, Competitor_Name, Competitor_Occupation, )

Game_Score ( Competition_No*, Competitor_No*, Game_Name*, Score )

f) Doggy Dips is a local company that offers swimming sessions for dogs. There are organised sessions in which up to 4 dogs can attend at any one time. They all last for 45 minutes. Owners can bring more than one dog at a time and can choose to swim with the dogs or not during a particular session. Each session is supervised by a company employee. The price of the session varies on the size of the dog and the popularity of the session.

Given the comments above and constructed First Normal Form (1NF) below:

Booking (Session_Number, Date, Time, Supervisor_No, Supervisor Training_Level, Dog_Number, Dog_Name, Breed_of_Dog, Age_of_Dog, Owner_Number, Owner_Name, Owner_Swimming, Price)

      The Second Normal Form is evaluated as:

Session (Session_Number, Date, Time, Supervisor_No, Supervisor_Name, Supervisor Training_Level)

Dog (Dog_Number, Dog_Name, Breed_of_Dog, Age_of_Dog, Owner_Number, Owner_Name)

Booking (Session_Number*, Dog_Number*, Owner_Swimming, Price)

Identify and write out the Third Normal Form (3NF) in relational schema form.

Session (Session_Number, Date, Time, Supervisor_No*)

Supervisor (Supervisor_No, Supervisor_Name, Supervisor Training_Level)

Dog (Dog_Number, Dog_Name, Breed_of_Dog, Age_of_Dog, Owner_Number*)

Owner (Owner_Number, Owner_Name)

Booking (Session_Number*, Dog_Number*, Owner_Swimming, Price)

QUESTION 4 – Relational Algebra (30 Marks)

Given the following set of table definitions:

Book ( Book_Id, ISBN, Title, Publisher, Year )

Student ( Student_Id, Student Name, Course Name, Age )

Author ( Author_Name, Date of Birth, Email )

Borrow ( Book_Id*, Student_Id*, Borrowed on )

Wrote ( Book_Id*, Author_Name* )

Classification ( Book_Id*, Genre_Name* )

Genre ( Genre Name, Description )

Produce the relational algebra definitions (NOT SQL) for the following queries:

a) List all students, by name and age, who are on course “Further Mathematics” [4 marks]

π _{Student_Name, Age} ( σ _{Course_Name = “Further Mathematics”} (_Student))

b) List the name and course name of all students who borrowed books in the “Sci-Fi” genre. [7 marks]

π _{Student_Name, Course_Name} ( σ _{Genre_Name = “Sci_Fi”} (_Student ⋈ (Borrow ⋈ (Book ⋈ Classification))))

c) List author names and the book title written, for those authors who had a book published between 2018 and 2021 inclusively. Note: Any sensible date format can be used [5 marks]

π _{Author_Name, Title} ( σ _{Year >= 2018 ^ Year <= 2021}  (_Author ⋈ (Wrote ⋈ (Book)))

d) List the students (by name) on the course "Database Concepts", who have borrowed a book written by any author born in 1988. We only require books that were published after 2015 and that are categorised as “Computing”. [8 marks]

π _{Student_Name} (

σ _{Course_Name = “Database Concepts” ^ Date_of_Birth >= 01.01.1988 ^ Date_of_Birth <= 31.12.1988 ^ Year >2015 ^Genre_Name = “Computing}      (_{Classification} ⋈ (Book ⋈ (Wrote ⋈ ( Author ⋈ (Borrow ⋈ (Student)))

e) List all titles, genre and descriptions of books written by authors born between 1^st September 1975 and 24^th March 1982 inclusively [6 marks]

π _{Title, Genre_Name, Description} (

               σ _{Date_of_Birth >= 01.09.1975 ^ Date_of_Birth <= 24.03.1982}

(_Genre ⋈ (Classification ⋈ (Book ⋈ ( Wrote ⋈ (Author)))