CSCI235-Database Systems

Task 1

A logistic company received a shipment contract to deliver parts from suppliers to customers. Suppliers ship parts to customers. A supplier may initiate one or more shipments to customers. In a shipment, there may be one or more parts. A customer may receive many shipments.

The conceptual model describing the relation of the entities in the above structural relation is given below.

The information of the shipments is depicted in the following object diagram that describes the structural relationship: 

Create an XML document to store the information described above. Your XML document must be well formed.

Deliverables

You are required to submit a file containing the XML document. You may use any text-editor to construct your XML document or any appropriate XML editor to generate the document. Your document must be well-formed, and it must not have any redundancies. Name your report Solution1.xml.

Task 2 

Document Type Definition

MyBookStore, a neighbourhood bookstore, has two branches selling hobbies books. We have information about the bookstore and the books sold in each branch. The first branch, named Fantastic Bookstore, is located at 10 Clementi Road and has a direct line telephone number 65123456. Fantastic Bookstore currently carries two categories of books. The first category is hobby book on cooking. The title of the book is “Healthy Cooking”. It is authored by “Jenny Penn” and published in year 2015. The price of the book is $28.00. The second category is hobby book on gardening. The title of the book is “Gardening Techniques”, authored by “Amy Brown”. The book was published in year 2014 and priced at $38.00. The second branch, named Excellent Bookstore, is located at 1 Bukit Timah Road. Instead of giving the direct lines to its staff, Excellent Bookstore uses a hotline telephone, and its number is 180065654321. Excellent Bookstore currently carries only one category of book. The category of the book is on photography. The title of the book is “Night photography”. It is authored by Peter Young and published in the year 2014. The price of the book is $100.

The above described information has the following structural relationship:

Write a well-formed XML document that contains information described above and add Document Type Definition (DTD) that uses external reference to validate the structures of the document. Your XML document must be well formed.

Deliverables You are required to submit a file containing the XML document and a file containing Document Type Definition (DTD). The document must validate against externally defined DTD. You are allowed to use any text-editor to create your XML document and DTD. Name the files Solution2.xml and Solution2.dtd respectively. 

Task 3

XML Schema

In a restaurant, a customer makes orders and a waiter is taking the orders. An order consists of list of foods ordered, the quantity ordered, and special instructions. To ensure the orders are served correctly, additional information such as the table number, date order, and the time of order are also recorded.

Write an XML schema that conform to the following requirements to describe the above information:

• A namespace “http://www.w3.org/2001/XMLSchema” for the elements and data types used in the schema.

• A namespace “http://www.w3school.com” for all the elements defined in the schema.

• A default namespace “http://www.w3schools.com”.

• To qualify all elements used by the XML instance document.

• An element table number with a minimum value of 1 and a maximum value of 50.

• An element food order consisting of three elements, the item description of type string, a quantity of type positive integer, and a special instruction of type string. The quantity is to be defaulted with a value 1.

• An element order date of type date.

• An element order time of type time

Your XML schema must be well formed, and you may use BaseX or XMLSpear to validate your XML document.

Deliverables

You are required to submit a report containing the XML schema description of the information. You may use any text-editor to construct your XML schema or any appropriate XML editor to generate the schema. Name your report Solution3.xsd.

Task 4

Analysis of relational schemas Consider the specifications of the sample database domains and the respective relational tables given below. For each one of the database domains listed below discover the respective sets of valid functional dependencies in the tables. For each one of the relational schemas identify its highest normal form. Remember that identification of a normal form requires analysis of the valid functional dependencies and the minimal keys. Provide justification of each answer. A solution with no comprehensive justification scores no marks.

a. In a product promotion fair, promoters are engaged to promote various products. A promoter may promote more than one product, and each product may be promoted by many promoters. A promoter is paid by commission, and the commission is computed based on a total sale for the product, for example, if the total sale for a product is below $1000, a promoter is paid 10% of the total sale for commission; if the total sale for a product is between $1000 and $5000, a promoter is paid 20%, etc. The information about the commission is stored in the following relational table. Commission (PromoterId, ProductId, TotalSale, CommissionPaid)

b. In a final-year-project selection process, students are to select one research topic for his/her project. Students are allowed to select the same research topic. For each research topics, supervisors are assigned to supervise it. A supervisor may be supervising up to two different research topics and each research topic may be assigned to different supervisors. For each of the research topic a supervisor supervises, a consultation day is allocated for the student to meet and discuss with the supervisor. This information of final-year-project selection are stored in the following relational table: FINALYEARPROJECT(supervisor, researchTopic, consultationDay, student)

c. Each customer is described by a unique customer number, a customer name, an address, and a postal code. Addresses are organized such that each address is associated to one postal code. A customer owns many different types of credit card, such as Visa or Master, and each credit card is described by a credit card number, a credit card type, and an expiry date. The information described are stored in a relational table CustomerCreditCard as shown here: CustomerCreditCard(custNum, custName, address, postalCode, cardType, cardNumber, cardExpDate)

d. A used car agency keeps the transaction of car buying and selling activities together with the commission earned. Sellers register their cars with the agency. The agency will look for interest buyers. The buyers may inspect, and test drive the car. If a buyer is interested, the buyer then purchases the car and completes a transaction. The agency keeps the information of the transaction in the following relational table. TRANSACTION(buyerNum, sellerNum, carNum, commission)

Deliverables Hand in a printed report, solution4.pdf, of your analysis and explanation on the highest normal form of the above relational tables. Make sure that your report is readable!