Go to the end of this document and start writing all your answers clearly stating the question number. E.g., Part A Question1 a).
- Submit this document, containing all your answers, through the sub-menu ‘Take-Home Exam’ under the ‘Assessment’ menu in the unit website (COIT20247).
- This Take-Home Exam comprises three parts, Part A, B and C.
- Students should attempt all questions in all parts.
- The total marks available in the Take-Home Exam are 35.
- The take-home exam should take you 2 (two) hours to complete. However, you will be given a 24-hour timeframe, between which you will need to download the exam from the unit’s Moodle website, complete it and upload it back.
PART A 17 MARKS
Students are required to answer ALL questions from this part.
All questions in this part relate to the ER model given below. The ER model illustrates the entities and relationships for AC installation model. Examine the ER model below and answer all the questions that follow.
Note: A customer can request for many jobs; a job can be an installation or a repair; an installation can be wall mounted or ducted installation.
ER model:
Question 1 4 Marks
Data Modelling
According to the ER model given above, answer either yesor noto the following questions:
- Can a job belong to more than one customer? (1 mark)
- Every installation must have at least one repair. (1 mark)
- A job type can be neither an installation nor a repair. (1 mark)
- If an installation is Wall/Window mounted, then it cannot be a ducted one.
(1 mark)
Question 2 4 Marks
- Explain the total and partial specialisations that have been used in the ER model.
(1 mark)
- List the reasons for using disjoint classification in the ER model. (1 mark)
- List the discriminator attributes that have been used in the ER model. (1 mark)
- Explain the use of multi-valued attribute in the ER model. (1 mark)
Question 3 5 Marks
Converting ER models
Convert the ER model given in Part A into a set of relations that satisfy Third Normal Form (3NF). You do not need to show your workings. You do not need to justify that they are in 3NF at this stage. You do not need to show sample data. Just show your relations. You should write your relations in either format shown below:
Question 4 4 Marks
Relational model and Normalisation
An incorrect relation Installation has been shown below. The primary key of the relation is InstallationId. Examine the relation and answer the questions that follow.
Note: A customer can request for many installations
Installation
| InstallationId | Date | Address | CustomerId | CustomerName | Cost ($) |
| 1 | 21/2/2020 | 333 Kent St, Sydney | C001 | John Smith | 2000 |
| 2 | 24/2/2020 | 400 Kent St Sydney | C001 | John Smith | 7200 |
| 3 | 24/2/2020 | 17 Elizabeth St, Campsie | C002 | Mary Smith | 2000 |
| 4 | 25/2/2020 | 432 Parramatta Rd, Redfern | C003 | Rose Miller | 2000 |
- Details related to a new Customer such as “C004, Krishna Kumar” have to be entered and stored in the above Installation relation. What will happen when such details only have to be stored without any relevant installation detail? (1 mark)
- What is the highest normal form that Installation relation satisfies and why?
(1 mark)
- Normalise the above-mentioned Installation relation into a set of relations that satisfy 3NF. You need not show your work out. Write the final relations only. You need to use the following format:
Customer (CustomerID, CustomerName) Order (OrderID, Amount, Date, CustomerID)
Foreign key (CustomerID) references Customer
(2 marks)
PART B 10 MARKS
STRUCTURED QUERY LANGUAGE QUESTIONS
Students are required to answer ALL questions from this part.
Each question is worth two marks (5 x 2 = 10 marks).
Formulate SQL queries to answer the following information requests. Use the relations Customer, Job, EmpJob and Employee that have been provided below.
Note: A Customer can request for many jobs; an employee can work on many jobs and in one job many employees can work.
Relations
Customer (CustomerID, CustomerName, ContactNumber, StreetAddress, City, PostCode)
Job (JobId, JobRequestDate, JobType, TotalCharges, CustomerId)
Foreign key (CustomerID) references Customer
Employee (EmployeeId, EmployeeName, Position)
EmpJob (JobId, EmployeeId, NumberOfHours)
Foreign key (JobId) references Job
Foreign key (EmployeeID) references Employee
Tables
Customer
| CustomerID | CustomerName | ContactNumber | StreetAddress | City | PostCode |
| 1 | John Smith | 0424100200 | 333 Kent St | Sydney | 2000 |
| 2 | Lisa Carpenter | 0325123321 | 400 Kent St | Sydney | 2000 |
| 3 | Peter Hairy | 0421456789 | 123 Marion St | Pyrmont | 2002 |
Job
| JobId | JobRequestDate | JobType | TotalCharges | CustomerId |
| 1 | 20/04/2019 | Installation | $400.00 | 1 |
| 2 | 20/05/2019 | Repair | $45.00 | 2 |
| 3 | 20/05/2019 | Installation | $600.00 | 1 |
Employee
| EmployeeId | EmployeeName | Position |
| 1 | Maria Kumar | Mechanic |
| 2 | Penny Swift | Electrician |
| 3 | Arthur Smoke | Welder |
| 4 | James Wheeler | Mechanic |
| 5 | Peter Rocket | Electrician |
EmpJob
| JobId | EmployeeId | NumberOfHours |
| 1 | 1 | 8 |
| 1 | 2 | 4 |
| 1 | 4 | 8 |
| 2 | 5 | 4 |
| 3 | 1 | 8 |
Note:
- You need to provide a general solution to each request. If the database contents change, each of your queries should continue to provide the information requested correctly.
- Simple queries are preferred; if your queries are unnecessarily complex you may lose marks.
- For the given sample data, your queries should be able to generate the same data and column names as shown in the result table for each request.
- You are not required to sort the results in any order unless requested.
- State any assumptions that you make to clarify your understanding of the information request.
Question 1 (2 marks)
Show the Employees who have never performed any Job.
| EmployeeId | EmployeeName | Position |
| 3 | Arthur Smoke | Welder |
Question 2 (2 marks)
Display the details of employees whose name contains the word ‘pen’.
| EmployeeId | EmployeeName | Position |
| 2 | Penny Swift | Electrician |
Question 3 (2 marks)
Display the details of employees who have performed only Installation type Jobs.
| EmployeeId | EmployeeName | Position |
| 1 | Maria Kumar | Mechanic |
| 2 | Penny Swift | Electrician |
| 4 | James Wheeler | Mechanic |
Question 4 (2 marks)
Display the details of employees who have performed only Repair type Jobs.
| EmployeeId | EmployeeName | Position |
| 5 | Peter Rocket | Electrician |
Question 5 (2 marks)
Display the details of employees who have performed at least one job in ascending order of their names.
| EmployeeId | EmployeeName | Position |
| 4 | James Wheeler | Mechanic |
| 1 | Maria Kumar | Mechanic |
| 2 | Penny Swift | Electrician |
| 5 | Peter Rocket | Electrician |
PART C 8 MARKS
SHORT-ANSWER THEORY QUESTIONS
Students are required to answer ALL questions from this part.
Each question is worth two marks (4 x 2 = 8 marks).
Question 1 (2 marks)
Briefly describe 3rd Normal Form of a Relation and provide the detailed steps to obtain the 3rd Normal Form of the relations for the question in Part A-Question-3C.
Question 2 (2 marks)
What is data integrity? By using the relations from Part B, explain what is meant by the following:
- Entity integrity rule
- Referential integrity rule
Question 3 (2 marks)
Why are locks used in multiuser database environment? Explain the difference between an exclusive lock and a shared lock.
Question 4 (2 marks)
Compare and contrast Data Warehousing against Database.
–End of Take-Home Exam—
Start your answers from here:
PART A
Q.1. a ) No.
Q.1. b ) No.
Q.1. c ) No.
Q.1. d ) Yes.
Q.2. a) Installation and repair are total specialisation from the above example so patients are one subtype whereas wall and ducted are partial specialisation.
Q.2.b) Disjoint rule is different as Job type must be in one subtype only. We can select option from superset in disjoint rule so one value must be picked. Installation must be specified at first to understand the requirement.
Q.2.c) Discriminator attribute: InstallationType
(WALLWINDOW_MOUNTED and DUCTED)
Q.2.d) Multivalued attribute is a type of attribute that which can associate with more than one value with the entity’s key.
For example, if a student plays to many sports than sports acts as a multivalued attribute.
Q.4.a) A relation can be in third normal formal form if it exists in 2nd normal form and non-key attribute is transitively dependent on the primary key.
Q,4, b) the table is in 2NF as it has no repeating groups along with partial dependency but has a transitive dependency, so it is not in 3NF.
PART B
Q.1)
SELECT EmployeeID, EmployeeName, Position
FROM Employee
EmployeeID not in (select EmployeeID from EmpJob);
Q.2.)
SELECT
* FROM Employee
WHERE EmployeeName like “*pen”;
Q.3)
SELECT
*FROM Employee as e inner join EmpJob as e.EmployeeId=ej.EmployeeIDinner join Job as j one ej.JobId=j.JobId j.JobType=” Installation”;
Q.4)
Select
*FROM Employee as e inner join EmpJob as e.EmployeeId=ej.EmployeeID inner join job as j on ej.JobId=j.JobId j.JobType=”Repair”;
Q.5)
Select EmployeeId, EmployeeName, Position FROM Employee EmployeeId in (select EmployeeId FROM EmpJob) ORDER BY Employee asc;
PART C
Q.1) The third normal form is part of a set of concepts for database normalization that includes first normal form (1NF) and second normal form (2NF). For a third normal form to happen the table must be in 2NF and there must not be any transitive dependency for non-prime attributes. It preserves lossless database transitions and functional dependencies are eliminated.
We have removed multivalued attribute like ducted and wall mounted to convert to 3 NF.
Q.2) Data Integrity is the accuracy and consistency of data throughout its span. Error checking and verification methods are commonly used to ensure the integrity of data. Data integrity can be demonstrated by the absence of the modification between two instances or between two changes of data record indicating the data is unchanged and preserved. Data integrity is usually imposed using standard procedures and rules during the database design phase.
Entity integrity rule is concerned with primary key concepts. The rule states that each table must have their own primary key, and their table must be unique, not null. For eg
Customer(CustomerID, CustomerName, CustomerNumber, StreetAddress, City, PostCode)
Here CustomerName, StreetAddress, City, PostCode cannot be primary key because different customers can have same name, address, city, postCode but customer id is always unique so it is considered as unique.
Q.3) Locks are used in multiuser database environment because database locks are used to lock some data in the database.to avoid two data from updating at the same time. Locks are used by only one user to update one specific data.
Exclusive Locks secures both recoverable and non-recoverable changes to the file property. They take care of only one transaction at a time. Transaction involving an exclusive lock must wait if any function is being owned by another exclusive lock or shared lock against the requested resources.
Shared locks support read integrity. They ensure that the record is not being updated during a read only payment. They are also used to avoid data changes between the time a data is read and the next sync point.
Q.4.
| Database | Data warehouse |
| It is a structured collection of stored. Manipulated and retrieved data | It is a vast range and combination of different types of data to analyse and generate reports. |
| Operation is different from data warehouse design as it mainly observes accuracy of the data when updating data in real time. | Its architecture provides a wide variety of data that is used for research purposes. |
| Focuses mainly on transactions with the help of queries. | Has data analytics capacity gathered from various sources and produces reports. |
| It provides online seat availability information | Information gathered is used for enhancements and business performances. |
| There are different kinds of databases like CSV, OLTP, and database queries operate less than a second. | It’s a category of OLAP which resides on top of other database layer and perform analysis. |
| It is optimised by a single point transaction for read-write operation. The OLTP database queries mostly respond in less than a second. | They are optimised to gather huge data sets to aggregate the datas they are designed to handle broad analytical queries, |
Uni Assignments For MIT students 