SQL代写 | CS5097/CS5098: Assignment 3

本次SQL代写是设计一个有关学生课程系统的数据库

CS5097/CS5098: Assignment 3 – Normalization, Transaction Management and File
Organization
Hand-In Deadline: 12 midnight Friday 8 November 2019
This exercise is part of the formal assessment of the course, and the work done must be by your own. You are
reminded to read the section on Cheating and Plagiarism in your student handbook. Marks for each question are
shown in brackets.
1. Consider the data file ‘sales.csv’, copied from the assessment page on MyAberdeen which has sales data for
a fictitious company with first line showing the sales attributes (fields) and each subsequent line showing a
separate sales record. Each sales record has values for sales attributes (fields). You are asked to store
ONLY A RELEVANT PORTION of the sales data in a relational database which can have tables only in
3NF. (You are reminded that a 3NF table necessarily should also be in 2NF and 1NF.)
i) Select a subset of attributes (fields) which you will store in your relational database and explain your
reasons for your selection. (1)
ii) Create the required database schema for the above requirements and list your database schema as a set
of relation schemas (table schemas). You are reminded that a single relation schema (table schema) is
written as shown below taking the DreamHome.Staff relation as an example:
Staff (staffNo,fName,lName,position, sex, DOB, Salary,branchNo)
Primary Key staffNo
Foreign Key branchNo references Branch(branchNo)
(4)
iii) Explain how each relation schema in your database is in 3NF. (3)
2. Consider the following Student table in a University Student Records database.
Every year when new students join the University, the admissions office produces a report consisting of
two parts as shown below:
Grand Total = 7
Discipline-wise Totals
Discipline Number of Students
Computing 2
Mathematics 3
Psychology 2
student
StudentId fName lName discipline
432987 Peter Gates Computing
452318 Mark Best Mathematics
485496 Joanne Perry Psychology
421267 David Murray Mathematics
489023 Jane Johnson Computing
458623 Nick Williams Psychology
415634 Mary Cummins Mathematics
Assume that admissions office created the following transaction to create the required report:
COMMIT;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT COUNT(*) AS ‘Number of Students’
FROM student;
SELECT discipline AS Discipline, COUNT(*) AS ‘Number of Students’
FROM student
GROUP BY discipline;
COMMIT;
Assume that when the above transaction has completed executing the first SELECT query that computes
the grand total, two new students, who were allowed to late register, added themselves to the Student table,
each running an insert query on the Student table:
INSERT INTO student VALUES(465891,’John’,’Jobs’, ‘Computing’);
INSERT INTO student VALUES(434567,’Linda’,’Ford’, ‘Psychology’);
Assume that the second SELECT query that computes discipline-wise totals in the transaction initiated by
the admissions office runs after the new students added themselves. The scenarios of different transactions
running on the Student table is shown below:
Time Step Transaction by the
Admissions Office
John Jobs’s Transaction Linda Ford’s Transaction
T1 COMMIT;
SET
TRANSACTION
ISOLATION LEVEL
READ
COMMITTED;
START
TRANSACTION;
SELECT COUNT(*)
AS ‘Number of
Students’
FROM student;
T2 INSERT INTO student
VALUES(465891,’John’,’Jobs’,
‘Computing’);
INSERT INTO student
VALUES(434567,’Linda’,’Ford’,
‘Psychology’);
T3 SELECT discipline
AS Discipline,
COUNT(*) AS
‘Number of Students’
FROM student
GROUP BY
discipline;
COMMIT;
i) Explain the problem with the above described scenario involving multiple transactions running
concurrently on the Student table. (4)
ii) Explain how you would solve the problem from (i) using your knowledge of transaction management.
(3)
3. Consider the following transaction scenario where Tis are transactions accessing several data items Xis:
Transaction Data Items Locked by Transaction Data Items Transaction is Waiting
for
T1 X2 X1,X3
T2 X3, X10 X7, X8
T3 X8 X4, X5
T4 X7 X1
T5 X1,X5 X3
T6 X4,X9 X6
T7 X6 X5
i) Produce a wait-for-graph for the above transaction scenario. (4)
ii) Using the wait-for-graph from part (i), determine whether the transactions are in a deadlock and
explain how you detected deadlock. (1)
iii) Explain how you would resolve the deadlock if it is detected in part(ii). (2)
4. Consider the Films table shown below and suppose that a disk page can store a maximum of 2 records:
i) With the help of simple sketches show the arrangement of Film records on disk pages using a
sorted file organization on Title. (1)
ii) With the help of simple sketches show the arrangement of Film records on disk pages after the
record for ‘Star Wars’ and ‘The Godfather’ are deleted. (1)
iii) How would your arrangement of records in (ii) have changed if you knew you had to insert records
for ‘Star Wars II’ and ‘The Godfather II’ after the deletion? (1)
Please submit your solutions document as a pdf file and name it with your name appended to AssignmentCA3 (e.g. Assignment-CA3_JoePublic.pdf). Upload the pdf file to MyAberdeen by selecting the
Assignment 3 link in the Assessment section. In the subject field type only ‘Assignment 3’ and nothing
else.
Please follow all the submission instructions to help us mark your coursework efficiently.
Film
Title Director Country Year Length
Jaws Spielberg USA 1977 125
Kramer V Kramer Benton USA 1979 105
Last Tango In Paris Bertolucci IT/FR 1972 129
Star Wars Lucas USA 1977 121
Superman Donner GB 1978 143
The Godfather Coppola USA 1972 175