Department of Mathematical Sciences

Database Application Lab Test July 1, 2021


MA332 Databases and data processing with SQL


Time allowed: You have been given a total of 2 hours to complete this test and scan and upload your
answers.

We expect this test will take you no longer than 90 minutes to complete and you have been given an additional 30
minutes in which to download the test paper and upload your answers to FASER. You must upload your answers
before the deadline shown in FASER. Answers uploaded after that time will receive a mark of 0.

If you are normally allowed additional writing time your deadline has been adjusted accordingly.

The deadline times are shown in British Summer Time (BST). Please check online for a conversion to your
local time if you will be undertaking your assessment outside the United Kingdom

Number of questions: 2
Candidates must attempt ALL questions.

Contacts
If you believe there is an error in this paper or you experience any other difficulties please email your module
lecturers [ Dr Fanlin Meng fanlin.meng@essex.ac.uk ]and [ Rafal Kulakowski rkulaka@essex.ac.uk ].

Additional instructions on completing and submitting your test answers:

 Type your registration number clearly at the top of the first page. Do not add your name as these will be
marked anonymously.
 Number your answers clearly
 Save your document as your registration number.
 We recommend you check your submission after you have uploaded to ensure you have uploaded the correct
document, and retain your email receipt from FASER.
 We do not anticipate any problems with FASER but if you do experience any difficulties uploading your
answers, you can email them to maths@essex.ac.uk with [module code] in the subject. You will still need to
upload to FASER but you can do this when you can access the system. This is just a back-up, please do not
email your answers if you are able to submit to FASER.
 Only your latest submission (within the deadline) will be marked.

Extenuating circumstances
We know that students are working under different conditions at the moment. If you encounter any difficulties with
your test that you believe has affected your performance you can inform the Exam Board of this by submitting an
Extenuating Circumstances form https://www1.essex.ac.uk/students/exams-and-coursework/ext-circ.aspx

Academic Offences
You are reminded that copying from textbooks, articles or any online or offline sources is plagiarism, which is an
academic offence. Your work must be entirely your own and collaborating with other students is collusion, which is
also an academic offence. Any student suspected of committing an academic offence will be interviewed in person
to determine whether the work they have submitted is entirely their own. More information about academic
offences can be found at https://www.essex.ac.uk/student/exams-and-coursework/about-academic-offences
MA332-7-SU Database Application Lab Test
1. Suppose the following Entity/Relationship diagram represents a databases design.
Answer the following questions:
i. State what entities, attributes and relationship are included in the E/R diagram. Describe the cardinality
ratio of the relationship.
[10 marks]
ii. Describe the databases represented by E/R diagram with a set of tables in the form:
TableName(element1, ... ,elementN). Indicate which elements represent Primary or Foreign Keys.
[15 marks]
iii. Use the information below to provide SQL commands that produce the tables from (ii.).
[15 marks]
Data-type information
• CID: Fixed length (5) character string.
• C Name: Variable length string (max. length = 50).
• Email Address: Variable length string (max. length = 60).
• PID: Fixed length (4) character string.
• Price: Numeric data type. Max. no. of digits 10, and 2 decimal places.
• Type: Variable Length String (max. length = 10)
• Quantity: Integer.
• Date: A date. Format: YYYY-MM-DD.
iv. Suppose you wish to connect and manage this database via Python. State what Python library would be
used for these tasks. Provide Python script that could be used to insert the following data into Product
table created in (iii.). (Assume that you have already connected to your database with Python and that the
relevant libraries are loaded)
[10 marks]
Table 1: Product Data
PID Type Price
101 Chair 75
102 Chair 135
103 Cupboard 85
104 Desk 160
105 Cupboard 95
2. Suppose a company provides a range of services for a subscription fee. Its database consists of two following
tables:
Table 2: Customer Data
CID Name Email Address
101 Jack jack@somewhere.com
102 Mary mary@somewhere.com
103 Lucy lucy@somwehere.com
104 John john@somewhere.com
105 Peter peter@somewhere.com
Table 3: Customer Activity Data
CID Service1 Service2 Service3 Total Activity
101 1 0 3 4
102 0 0 0 0
103 2 1 3 6
104 3 3 0 6
105 0 0 0 0
Table 2 stores the basic customer data and Table 3 stores the information about how often the customers have
used services provided by the company (Total Activity attribute summarises the number of times a customer
used company’s services.)
i. The database includes following functional dependencies:
{CID} → {EmailAddress} and {CID} → {TotalActivity}
State which one of the Armstrong’s secondary rules is used to imply that:
{CID} → {EmailAddress, TotalActivity}
[5 marks]
ii. Provide SQL commands that can create a new table that combines columns CID, Email Address from
Table 1 and Total Activity column from Table 2.
[10 marks]
iii. Use a correlated subquery and EXISTS command in SQL with a table created in [ii.] to obtain a table of
customers with Total Activity = 0.
[15 marks]
iv. Suppose that you have created a table only for customers listed in [iii.] and now you wish to apply a
variety of advertising strategies to increase their activity and record this data for future analysis.
Assuming a Strategy is a fixed length character attribute, use SQL command to add corresponding
column to this filtered table.
[5 marks]
3. Suppose the following tables represent a database that describes Client Data of a Data Science company.
Table 4: Clients Data
CID Name Course
101 Bankxyz Bankxyz@somewhere.com
102 CarsA CarsA@somewhere.com
103 UniversityB UniversityB@somewhere.com
104 PhonesX PhonesX@somewhere.com
105 TransportZ TransportZ@somewhere.com
Table 5: Payments Data
CID Service AmountPaid
101 DB Engineering 5000.00
102 DB Engineering 0
102 Analytics 0
103 DB Engineering 4000.00
104 DB Engineering 5000.00
104 Analytics 3500.00
104 Predictive Modelling 7500.00
105 DB Engineering 5000.00
Table 6: Costs Data
Service Cost
DB Engineering 5000.00
Analytics 3500.00
Predictive Modelling 7500.00
i. Suppose we wish to combine Tables 4,5 and 6 in a single table called Client Payments Data. State what
term describes this type of database management strategy.
[5 marks]
ii. Comment on what could motivate making such structural changes to a database in general.
[5 marks]
iii. Describe how many tupples and attributes would the Client Payments Data consist of.
[5 marks]