Exam 5 November 2019, questions PDF

Preview

Summary

database ...

Description

Question 1: (24 marks). ABC Music Limited has many shops selling musical instruments. Shops are organised into regions. The management would like to build a database to manage customer data. The attributes and functional dependencies among attributes are as follows: custID à custName, phoneNo itemNo à description, price shopNum à shopAddr, regionCode shopAddr à regionCode regionCode à regionName custID, itemNo, shopNum, salesperson à sale-date, price Semantics of attributes are as follows: •

custID, custName and phoneNo refer to the unique No., as well as name and phone No. of customers.

•

itemNo, description and price are the unique No., as well as description and price of instruments for sale.

•

shopNum and shopAddr are the unique No., as well as address of shops.

•

regionCode and regionName are the unique code, and name of regions.

A database designer comes up with the following design for the database: Customer(custID, custName, phoneNo) Instrument(itemNo, description, price) Shop(shopNum, shopAddr, regionCode, regionName) Transaction(custID, itemNo, shopNum, salesperson, sale-date, price) Based on the given FDs, answer the following questions: 1.1. (4 marks) Give the minimal basis for the given FDs. Show your working. 1.2. (4 marks) Explain if relations Customer and Instrument are in BCNF. 1.3. (4 marks) Underline the primary key for relation Transaction. Explain your answer using the given FDs. 1.4. (4 marks) Relation Shop is not in BCNF or 3NF. Explain the reason using the given FDs. 1.5. (4 marks) Decompose relation Shop into relations in BCNF or 3NF. 1.6. (4 marks) For each relation after decomposition in Question 1.5, underline the primary key and denote any foreign key with an asterisk (*).

ISYS1055/1057 Database Concepts

Page 2/6

Question 2: (51 marks in total). Schema for the Academics database is as follows: DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode) ACADEMIC(acnum, deptnum*, famname, givename, initials, title) PAPER(panum, title) AUTHOR(panum*, acnum*) FIELD(fieldnum, id, title) INTEREST(fieldnum*, acnum*, descrip) The semantics of most attributes are self-explanatory. For each relation, the primary key is underlined and any foreign key is denoted by an asterisk (*). Some additional information for relations is as follows: • DEPARTMENT: Each academic department (deptnum) belongs to an institution (instname). • ACADEMIC: Each academic (acnum) belongs to one department (deptnum). • AUTHOR: A paper (panum) may be written by more than one academic (acnum). • FIELD: The relation describes research areas, where id is a classification code like B.1.1. • INTEREST: The relation describes that an academic may have research interests in several fields (fieldnum), and provide description (descrip) of his/her research. 2.1. (4 marks) Consider writing an SQL query to compute the total number of papers by each department and output the deptnum, deptname, and total number of papers. Point out errors in the SQL query below and give the correct SQL query. select DEPARTMENT.deptnum, deptname from DEPARTMENT, ACADEMIC, AUTHOR group by DEPARTMENT.deptnum; 2.2. (4 marks) Consider the SQL query below. Explain first what the subquery does and then what the whole SQL query does. Literal explanation will receive zero mark. select ACADEMIC.acnum from ACADEMIC, INTEREST where ACADEMIC.acnum=INTEREST.acnum and fieldnum = 342 and INTEREST.acnum in (select acnum from INTEREST where fieldnum != 342);

ISYS1055/1057 Database Concepts

Page 3/6

Write one SQL query for each question below. Your SQL queries should not produce duplicates in their output, however, you should use DISTINCT only if it is necessary. 2.3. (4 marks) How many institutions in Queensland are there in the current database? Your query must take care of all spelling variations for Queensland, including "Qld" and "QLD". 2.4. (5 marks) List the papers that have a title containing the string "Super" and having an author with acnum>100. Return the panum and title of these papers. 2.5. (6 marks) Which papers have at least 3 authors? List the panum of these papers and their number of authors, ordered in decreasing number of authors. 2.6. (6 marks) List the family name and given name of academics who have not written any papers. Output should be in the alphabetical order of the family name and given name. You must use the NOT EXISTS operator. 2.7. (7 marks) List the panum of papers that are written by academics from different departments. 2.8. (7 marks) Which fields have the largest number of interested academics? Return the field number and title of the field. 2.9. (8 marks) List the institutions where every academic has some research interest.

ISYS1055/1057 Database Concepts

Page 4/6

Question 3: (45 marks in total). 3.1. (20 marks) The ER diagram below is for a course management database. Map the ER diagram into a relational database schema. For each relation of the resultant database schema, underline the primary key and denote any foreign key with an asterisk (*).

ISYS1055/1057 Database Concepts

Page 5/6

3.2. (25 marks) You are asked to design the train service management database for the M3 transport corporation in the city of Big M. Description of the miniworld is given below. • There are several train lines from the city centre to suburbs. Each line has a unique name and is also described by the distance between the city centre and the terminus station. For example, the Lake Villa line runs from the city centre to Lake Villa and has a distance of 30 kilometres. • Each station has a unique name, as well as a station master name and a contact phone number. • A station may have several platforms numbered from one. For example, Happy Valley has 8 platforms from No. 1 to No. 8. Smallville has 4 platforms from No. 1 to No. 4. • Trains are identified by a unique number. Other information about trains includes passenger capacity, model, manufacturer and year-made. • Drivers are employees of M3 and are described by a unique employee No, first name, last name, and birth date. • Each line has inbound and outbound train runs that are numbered from one. For example, Eastern line has inbound run No. 1, outbound run No. 1, inbound No. 2, outbound No. 2 etc. Each train run is assigned a primary driver and an assistant driver. Each train run is also assigned a train. On each line, a train run is timetabled to depart a platform of a station at certain time. Express train runs do not stop at all stations on a line. Draw an Entity Relationship (ER) diagram for the database. You must use only symbols in the lecture notes. Make assumptions where necessary and explain any constraints that can not be expressed in the ER diagram.

-------- THE END --------

ISYS1055/1057 Database Concepts

Page 6/6...