数据库考试代考 RELATIONAL DATABASES代写

COMPS359 RELATIONAL DATABASES: THEORY & PRACTICE

Examination

数据库考试代考 HIS IS AN ONLINE OPEN-BOOK EXAMINATION 1.This examination paper is available on OLE Online Exam Paper(s) page. 2.You should answer the examination

HIS IS AN ONLINE OPEN-BOOK EXAMINATION 数据库考试代考

This examination paper is available on OLE Online Exam Paper(s) page.
You should answer the examination paper using your OWN efforts only. Anyplagiarism behaviour discovered will have serious consequences, including getting zero mark for this examination.
This is an open-book examination. You can read books and access the Internet. However, you cannot copy answers from any source.
Use your own paper to answer the questions of this examination paper in English. You mayoptionally print our answer book (on OLE) for writing your answers.
You can start as soon as you successfully download the question paper.
Read the instructions in the examination paper carefully and writethe question numbers and answers clearly. It may not be possible to award marks where the writing is very difficult to read. You are required to WRITE your answers and typed answers are NOT allowed (unless prior approval is given).
At the end of the examination, scan your answers and check each page. Make sure the image of each page is sharp enoughto be seen. Generate and upload a PDF file containing your answers to OLE “Online Exam”. Normally only this OLE version will be marked.

Part I 数据库考试代考

Answer ALL questions in this part. Part I carries 60% of the total examination marks.

Question 1 (5 marks)

(a) Discuss the importance of data for banks.

Complete (b) to (d) by filling in an appropriate word(s) or phrase.

(b) The ________ schema in the three schema architecture is concerned with how data is stored and accessed.

(c) ________ makes a copy of database data available locally so that users can perform their actions without accessing data from remote database servers.

(d) Data is said to be ________ if it is appropriate for the use to which it is being put.

Question 2 (5 marks)

Consider the following diagram which shows occurrences of the Treats relationship connecting occurrences of the entity types Doctor and Patient. The diagram represents data at a specific instant in time. It may not be typical for all occurrences of relationships that may occur at different times.

(a) State the number of occurrences of the Treats relationship shown in the diagram.

(b) What can you deduce about the cardinality and participation conditions of the Treats relationship based on the occurrences given?

(c) From your answer to (b) and the further requirement that patients should be treated by one or more doctors, draw an E-R diagram showing the Treats relationship between the two entity types Doctor and Patient. Your diagram should include cardinality and participation conditions.

Question 3 (5 marks)

The following figure gives a fragment of an E-R model for Student and Tutor. Every student is taught by one and only one tutor but there is at least one student taught by a tutor.

Assume that the attributes StudentID, Address, TutorID, and Name are defined over the domains StudentIDs, Addresses, TutorIDs, and Names, respectively. Write down a relational representation for this E-R fragment that is consistent with the relational theory taught in Block 2.

Question 4 (5 marks) 数据库考试代考

The Hospital scenario used in the course materials has a relational representation that includes the following relational headings:

Nurse(StaffNo, NurseName, WardNo)

Supervises(StaffNo, Supervisor)

Ward(WardNo, WardName, NumberOfBeds)

The attributes StaffNo of Nurse and Supervises, and the attribute Supervisor are defined using one domain. The attributes WardNo of Nurse and Ward are defined using one domain. All other attributes have their own domains.

Note: You only require the information given in the above relational headings and the domain descriptions to answer this question.

Write down a relational algebra expression which will evaluate to give each of the following:

(a) the staff number and the ward number of each nurse in the Nurse relation,

(b) the staff number, ward number and the number of beds in that ward for each nurse in the Nurse relation, and

Question 5 (5 marks)

The following relation models part of the operation of a software company:

TaskAllocation (StaffId, ProjectId, StaffName, ProjectName, ExpectedStartDate, ExpectedEndDate)

A tuple in this relation represents the fact that a particular member of staff with a name is expected to be working on a particular project with a project name, and this period of work has an expected start date and an expected end date (the duration of a task allocation varies).

The following functional dependencies hold:

F1: StaffId → StaffName
F2: ProjectId → ProjectName
F3: StaffId, ProjectId → ExpectedStartDate
F4: StaffId, ProjectId → ExpectedEndDate

(a) Derive a further non-trivial functional dependency from F1 to F4.

(b) Explain why TaskAllocation is not in second normal form (2NF). Using non-lossy decomposition, decompose TaskAllocation into relations which are in 2NF.

(c) Write down a set of relations obtained from TaskAllocation by non-lossy decomposition which are in Boyce-Codd Normal Form (BCNF). Justify your answer.

Question 6 (5 marks) 数据库考试代考

An SQL table Mouse is shown below:

brand	modelNo	price
Logitech	M105	78
Genius	DX120	50
Microsoft	Comfort 3000	139

Write down whether each of the following SQL statements is valid or invalid. If it is valid, state the results (including the heading) after its execution. If it is invalid, give the reason.

(a)

select brand, COUNT(price) from Mouse;

(b)

select brand, AVG(price) from Mouse
  where AVG(price) > 58
  group by brand;

(c)

select brand, price from Mouse
  where price < (select price from Mouse where modelNo='Comfort 3000');

Question 7 (5 marks)

The SQL table Mouse is also used in this question with an attribute resolution added:

brand	modelNo	resolution	price
Logitech	M105	1200	78
Genius	DX120	600	50
Microsoft	Comfort 3000	1800	139

where brand has at most 20 characters, modelNo has at most 25 characters, resolution is a positive integer and the highest price is $999.9. The primary key is brand and modelNo.

Using SQL, perform the following:

(a) Create the Mouse table with a default model number of “M105”.

(b) Assume there are more rows of data, display the price and resolution of the most expensive mouse of each brand in descending order of the resolution.

(c) Find the brands and resolutions of the mouse, of which the prices are not more than the average price of all the mouses.

Question 8 (5 marks)

Consider the tables part and supplier with headings shown below. The supplier_name column of the part table is a foreign key referencing the supplier table, and representing the supplies relationship.

Complete the following SQL statements to define the part and supplier tables described above. In your definition, you should include the appropriate constraints to ensure that

the part_nocolumn should take values between p01and p99 inclusively, and
every supplier provides at least one part.

(a)

CREATE TABLE supplier
(supplier_name VARCHAR(25) NOT NULL,
location VARCHAR(25) NOT NULL,
PRIMARY KEY (supplier_name),

(b)

CREATE TABLE part
(part_no CHAR(3) NOT NULL,
part_name VARCHAR(25) NOT NULL,
supplier_name VARCHAR(25) NOT NULL,
stock_level INTEGER NOT NULL,
price INTEGER NOT NULL,
PRIMARY KEY (part_no),

Question 9 (5 marks)

The figure below shows a first attempt at a fragment of a conceptual data model for a delivery company.

As you can see from the entity-relationship diagram:

Each Depot can deliver to one or more Customers.
Each Customer can have a delivery from zero, one or more depots.

Entity types

Depot(IdCode, Town, ContactPhone)
Customer(Reference, Location)

Assumptions

CanDeliverTo represents the fact that a depot can deliver to a customer, it does not represent the specific actual deliveries made from depot to customer.

The delivery company now also wants to record the estimated distance and estimated journey time for each delivery.

Revise the E-R model fragment above (that is, re-draw the above E-R diagram and give the additional entity type) so that the amended model fragment is consistent with the new requirement.

Question 10 (5 marks) 数据库考试代考

(a) Explain why we need to perform denormalisation. Give one disadvantage of doing so.

(b) Give three reasons why it may be necessary to restructure a database after it has been installed and accepted by the client.

Question 11 (5 marks)

(a) What is location independence? What is the name of the schema needed for a distributed DBMS to find the location of the data.

(b) Briefly describe the two major roles of a replication server?

Question 12 (5 marks)

(a) PDO is a PHP API for database access. State one advantage and one disadvantage of using it instead of using a database’s PHP API for accessing the database.

(b) PDO has one method $db->exec() for modifying data and another method $db- >query() for retrieving data. Discuss why we need two methods instead of one.

Part II 数据库考试代考

Answer TWO questions from this part. Part II carries 40% of the total examination marks.

Question 13 (20 marks)

A computer repair shop helps registered customers to repair and/or maintain a variety of computers. The shop optionally provides maintenance services in three types, as follows:

Brozen: shop is responsible for emergency repairs only.
Silver: shop handles emergency and regular maintenance of some items.
Gold: shop manages all aspects of the maintenance of the computer(s).

The following is a fragment of an E-R model of the data requirements for this business where only the most recent contract of each customer are recorded.

Entity types

Computer (ComputerID, Name, DateManufactured)
Contract (ContractNo, SignedDate, StartDate)
Customer (CustomerNo, Name, RegistrationDate, Address)
MaintenanceType (Type, Description)

Additional constraints

c.1 The date manufactured for a computer must be on or before the signing date for a contract on that computer.
c.2 Values of the attribute Type can only be from the set: {‘Bronze’, ‘Silver’, ‘Gold’}.
c.3 A computer can be managed for maintenance only if there is a contract relating to that computer.

Assumptions

a.1 Only the most recent contracts are of interest to the shop. (No history of past records is maintained by the shop).

(a)

Give a relational representation of the E-R model fragment above. Do not add anything that is not explicitly represented in the fragment. Include comments to relate the parts of your relational representation to the corresponding aspects of the E-R model fragment.

(b) Suppose the assumption a.1 is removed and a customer’s past history of computer maintenance, as well as the most recent one, are to be held by the shop. This will involve the shop recording the date of a contract was, or will be, terminated.

How would you extend the above E-R diagram, entity types and constraints to model this new requirement? Redraw the part of the E-R diagram which has changed and show the revisions needed to the entity types and constraints to meet the new data requirements.

Question 14 (20 marks) 数据库考试代考

This question is based on the following tables of an online shop selling phones to customers:

Phone(phoneID, brand, price)
Primary Key phoneID

Customer(customerID, customerName)
Primary Key customerID

ShoppingCart(cartID, customerID, phoneID, phoneCount)
Primary Key cartID
Foreign Key customerID references Customer(customerID)
Foreign Key phoneID references Phone(phoneID)

where phoneID consists of at most 12 characters, brand is not null and at most 25 characters, the maximum price is $99999.9; customerID consists of 10 characters, customerName is at most 50 characters; cartID is unique and consists of 20 characters; phoneCount is the number of phones (with the same phoneID) in the shopping cart.

Using a single SQL statement, perform each of the parts:

(a) Create the table ShoppingCart with a default phoneCount of 1;

(b) Create a domain priceDomain for the price of the table Phone;

(c) Add a new attribute “newModel“, which indicates if the phone is a new model or not (with value ‘Y’ or ‘N’), to the Phone table;

(d) Create a view PhoneSummary which contains the following summary information of each phone (in a row): phone ID (with name phID), brand, total value of all the phones sold (with name totalValue);

(e) List the brand and phone ID of the phones together with the customer IDs and names of the customers buying the associated phone, sorted by the brand;

(f) List each customer ID and name of the customer with the total value of all the phones bought by this customer;

(g) Display the percentage of phones with different phoneIDs in Phone table bought by at least 100 customers;

(h) Show the customer ID and total amount of money paid by the “largest” customer (whose total amount is the largest among all customers). You can assume there is only one such customer.

(i) Change the brand of a phone with phone ID “A12” to “MyBrand” and its price to $1588.

Question 15 (20 marks) 数据库考试代考

(a) In transforming an entity-relationship model into a first-cut design for a database, we have different options.

In each of the following aspects, (1) outline the options available, and (2) the criteria used to select an appropriate option.

(i) relationships

(ii) complex data

(iii) general constraints

(b) In distributing data of a table amongst the different locations of a distributed database system, we have different options.