Download Report

CORK INSTITUTE OF TECHNOLOGY
INSTITIÚID TEICNEOLAÍOCHTA CHORCAÍ
Semester 1 Examinations 2014/15
Module Title: Data Management Systems
Module Code:
DATA8002
School:
Science & Informatics
Programme Title:
Higher Diploma in Science in Data Science & Analytics
Programme Code:
SDAAN_8_Y4
External Examiner(s):
Mr. Barry Guiney, Prof. Eamonn Murphy
Internal Examiner(s):
Mr Larkin Cunningham
Instructions:
Answer 4 out of the 5 questions.
All questions are worth 25 marks each.
Questions 1, 2 and 3 require that you consult the relational database
schema in Appendix A at the end of this paper.
Duration:
2 hours
Sitting:
Winter 2014
Requirements for this examination:
Note to Candidates: Please check the Programme Title and the Module Title to ensure that you have received the
correct examination paper.
If in doubt please contact an Invigilator.
Page 1 of 5
Question 1
Refer to the relational database schema described in Appendix A when answering (a) to (e) below.
(a)
Write the SQL statements to create the Car, Salesrep, Customer and Sale relations, including
all the necessary constraints defined above and using appropriate data types. For the Sale
relation, you should include foreign key constraints and the action to be taken for updates and
deletes.
[11 Marks]
(b)
Write the SQL statement to add a column called PPSNum to the Salesrep table. Choose an
appropriate datatype, considering that 1234567X is an example of a PPS Number.
[2 Marks]
(c)
Write an SQL statement to add a new record to the Car table – you can make up the necessary
details for the record, i.e. Reg_No, Model, etc., but you must ensure that the record can be
inserted based on your answer to part (a) of this question.
[2 Marks]
(d)
Write an SQL statement to decrease the price of all cars that have not been sold by 10%.
[5 Marks]
(e)
Write an SQL statement to delete the customer whose customer number is 25. Given the
referential integrity constraints you chose for this schema in part (a) of this question, explain
under what circumstances this delete operation can proceed.
[5 Marks]
Question 2
Refer to the relational database schema described in Appendix A when writing the SQL queries for (a)
to (e) below.
(a)
List full details of all red cars whose engine is at least 1.4L and costs between €10,000 and
€20,000 – arrange the output in ascending order of engine size and within engine size in
decreasing order of price.
[5 Marks]
(b)
List all customers who have bought more than 1 car from the dealership.
[5 Marks]
(c)
List full details of all cars that have been sold to a customer named Sarah Buckley who lives in
Kinsale.
[5 Marks]
(d)
For every car that has been sold, list full details of the car together with the name of the
customer who bought the car – arrange the output in alphabetical order of customer name and
within customer name in descending order of the date of the sale.
[5 Marks]
(e)
For every sales representative, list the sales representative’s number, name and the number of
sales they have made together with the average value of their sales– arrange the output in
descending order of the average value of their sales.
[5 Marks]
Page 2 of 5
Question 3
(a)
Draw an Entity Relationship (ER) diagram for the relational database schema given in
Appendix A, using Crow’s Foot notation, and clearly state any assumptions you have made.
[6 marks]
(b)
Explain what a recursive relationship is? Suggest a potential recursive relationship that could be
added to the database schema in Appendix A.
[3 marks]
(c)
In terms of ER Modeling, explain what is meant by each of the following terms. Refer to the
relational database schema given in Appendix A or your ER diagram in part (a) to illustrate your
answer in each case with an example:
1. Degree of a relationship
2. Strong Entity Type
3. Weak Entity Type
[6 marks]
(d)
Explain with the aid of an example the following issue associated with ER Modelling:
“The Fan Trap”
[3 marks]
(e)
Discuss the benefits associated with the use of “Views” in a relational database.
[4 marks]
(f)
Explain what a “Domain” is in terms of how it applies to attributes in a base relation. Give 2
brief examples of domains.
[3 marks]
Question 4
(a)
Compare and contrast file-based systems with Database Systems, including the advantages
and disadvantages associated with each.
[9 marks]
(b)
List and explain each of the layers of the ANSI-SPARC Three-level Architecture. Explain
Logical Independence and Data Independence in relation to the ANSI-SPARC Three-level
Architecture.
[8 marks]
(c)
Explain what is meant by the following replication architectures:
 master-slave
 multimaster (or peer-to-peer)
Describe one advantage and one disadvantage for each replication architecture.
[8 marks]
Page 3 of 5
Question 5
(a)
Briefly explain what a “shared-nothing architecture” is.
[2 marks]
(b)
Explain what is meant by the “CAP theorem” and “eventual consistency”.
[5 marks]
(c)
The relational model is currently the most widely implemented general-purpose data model,
trusted by major international corporations to manage their data. However, there are several
emerging data models grouped under the “NoSQL” banner that are suited to specific use
cases.
Describe in detail, with the aid of at least one use case for each data model, the following
emerging NoSQL data models and in each case compare and contrast the data model with
the relational data model providing at least one advantage and one disadvantage over the
relational data model.
(1) Key-value store data model
[9 marks]
(2) Graph data model
[9 marks]
Page 4 of 5
Appendix A
The following is a relational database schema for a used-car dealership (the primary key attributes are
underlined):
Car(Reg_No, Model, Manufacturer, Year, Engine, Colour, Mileage, Price)
SalesRep(Rep_No, Name, Phone)
Customer(Cust_No, Cust_Name, Address, Phone, Email)
Sale(Reg_No, Cust_No, Sale_Date, Sale_Price, Rep_No)
where:




Car contains details of used cars available for sale
Salesrep contains details of the sales representatives working in the dealership
Customer contains details of all customers
Sale contains details of sales of cars to customers
The following constraints apply:
 Car relation
o All fields are required fields.
o Mileage has a default of 10,000.
o Colour has a default of Black.
 SalesRep relation
o All fields except Phone are required fields.
o Grade has a default value of ‘I’.
 Customer relation
o All fields are required fields except for Email.
 Sale relation
o All fields except for Rep_No are required fields.
Page 5 of 5