Assignment Specification:

Task 1

Analysis of relational schemas and normalization

Consider the following conceptual schema of a sample database domain that contains information about undergraduate and postgraduate students, subjects, and enrolments performed by the students.

A database designer applied a process of logical design and he/she transformed the conceptual schema into the following collection of relational schemas.

STUDENT(snumber,first-name,last-name,hobby,jobs,facebook-friends)

primary key = (snumber)

SUBJECT(code,title,session,year,credits)

primary key = (code)

candidate key = (title)

ENROLMENT(snumber,code,title,enrolment-date)

primary key = (snumber,code, enrolment-date)

foreign key 1 = (snumber) references STUDENT(snumber)

foreign key 2 = (code) references SUBJECT(code)

foreign key 3 = (title) references SUBJECT(title)

• Find all functional and multivalued dependencies in the relational schemas STUDENT, SUBJECT, and ENROLMENT.
• Find all minimal keys in the relational schemas STUDENT, SUBJECT, and ENROLMENT. List the derivations of all minimal keys.
• For each one of the relational schemas finds the highest normal form a schema is in. List the justifications for each highest normal form found.
• Decompose all relational schemas that are not in 4NF into 4NF. List all relational schemas obtained from the decompositions.

Task 2

Analysis of relational schemas and normalization

Consider the following un-normalized relational table on an online retail store orders and payments information:

An online retail store would like to create a database to keep track of its sales activities. The information recorded in the database supposed to include customer number that identifies each customer, customer’s first name, last name, the unique order number of orders a customer made, the date when an order was made, unique product number of products included in orders, product description, sequence number listing the sequence when a product is included in each order as well as the quantity for each product made in each order. The retail store would also like to store the payment information such as a credit card number of credit card. that was being charged to, the payment date, and the amount paid. The online retail store allows customers to pay using any one of the credit cards the customers own, as long as the credit card is valid.

A database designer created the following relational schema:

CUSTORDER(custNum, custFName, custLName, orderNum, orderDate, prodNum, prodDesc, itemNum, quantity, cCardNum, paymentDate, amountPaid)

Decompose the relational schema into the smallest number of relational schemas each one in fourth normal form (4NF) and to explain or justify that each schema is in 4NF.

To justify that the relational schemas obtained from the decomposition are in 4NF you must find all functional and multivalued dependencies valid in each relational schema, you must find the minimal keys, and then apply the definitions to support your justification. Note, that a relational schema is in 4NF when it is in BCNF and it does not have any nontrivial multivalued dependencies. It means, that first, you have to prove that a schema is in BCNF and later show that it has no nontrivial multivalued dependencies. Please keep in mind that the smallest number of 4NF relational schemas is expected.

Task 3

Indexing

• Consider the TPCHR benchmark database created through the processing of CREATE TABLE statements include in the file TPCHR.

An index ordersIdx(o_orderStatus, o_clerk, o_totalPrice) has been created over the relational table PART.

Construct SELECT statements that will use the index partIdx in the following ways:

1. Execution of the first SELECT statement must traverse the index vertically and it MUST NOT access a relational table ORDERS.
2. Execution of the second SELECT statement must traverse the index vertically and then horizontally at the leaf level of the index and it MUST NOT access the relational table ORDERS.
3. Execution of the third SELECT statement must traverse the leaf level of the index horizontally and it MUST NOT access the relational table ORDERS.
4. Execution of the fourth SELECT statement must traverse the index vertically and it MUST access the relational table ORDERS.
5. Execution of the fifth SELECT statement must traverse the index vertically and then horizontally and it MUST access a relational table ORDERS.

• Referring to the same TPCHR conceptual schema, we would like to improve the performance of the following group of queries:

1. Find the total number of orders that include at least one item (relational schema LINEITEM) with tax rate = 0.1 from orders made in the year ‘1998’.
2. Find the comment (L_COMMENT) of all items (relational schema LINEITEM) included in the orders that were handled by a clerk with the number ‘clerk#000000136’.
3. Find the names of customers who submitted orders in a given year.

Find an index that speeds up the processing of the statements in the best possible way listed above, and create the index. You are only allowed to create ONE index per query. The best possible way means the database system will execute a query and uses the index proposed by you.

Use ‘Explain plan for …’ to generate the execution plan for each of the SELECT statements that you have proposed and created. Next use the ‘SELECT * FROM TABLE(dbms_xplan.display) to display the generated execution plan to show that your proposed SELECT statements indeed perform according to the specification.

The post CSCI235: An Online Retail Store would like to Create a Database to Keep Track: Database Systems Assignment, SIM appeared first on Assignment Help Singapore No 1 : Essay & Dissertation Writers, SG.