Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

these are clearer pics of Q3 and Q4 QUESTION 1 You are helping a homestay rental company to develop a new database. Below is the

image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
these are clearer pics of Q3 and Q4
image text in transcribed
image text in transcribed
QUESTION 1 You are helping a homestay rental company to develop a new database. Below is the collected information about the company: - They are the middleman between owners and customers. - Each owner may have many houses to rent. However, each house can only be owned by one owner. - The customer can book the house many times at different time frames. - Payment is charged on daily basis according to specific date. (a) Drafts TWO sets of business rules based on the information given. [2 marks] (b) Design the entity relationship diagram (ERD) by using the Crow's Foot Model. [Hints: must include primary key (PK), foreign key (FK), AT LEAST 2 attributes (not including PK and FK) for each entity, and associate entity if any] [8 marks] QUESTION 2 Figure 1. Day Care (Cenplete) Figare 2. Day Care Part I Figure 1 shows the nursery information of a kindergarten. Figures 2 and 3 further show the information in details by breaking the table in Figure 1 into sub tables. The shown attributes are FatherName, MotherName, Hometddress, Region, RegisDate, Contact, ChildID, ChildName, Class, Teacher, Age, FeesAmt, and payment stahus for each month (from January to December). Each month, an invoice is provided by using the children name after their parents have made the payment. Recently, the kindergarten wants to have a database system to keep the records. Suppose you have been hired to implement the system and decided to start from normalisation. (a) Identify the attributes that is suitable to use as composite key in first normal form and justify your result. [1. mark] (b) Please identify the composite keys and attributes for the first normal form. [3 marks] (c) Complete the normalisation process by performing second and third normalisation. [6 marks] diagan (EKD) shown in Figert 4. Suppote yos are Mian Behe the following question YY'YY-MM-DD. aftribetes. [3 marta] (b) Lint ech order sumber with date, tutwonn nene and canume nanber. Sort the recont by date. [2.5 marta] Here is the SQC wrinen by Nain: SELECT Ordertio, Date, Cuatomertis, Cantymetiane, SuturitPrice " Quantity AS 'TotalPrice'. FRCM PROOUCT AS R, ORDER_LIE AS orth, ofoch as erd, Whene p.ProductNo = orve. Productsve AND cedt. Orderilo = erat. Ordertis AND CustemerNo = CuatNog Chock the eorrotaen of the query and jurify your anaw. [1.5 murks [B marks] Coatinned. F Treual natagatemment QUESTIO8 4 (i) Durint dutabaie initial stadb, what quescont soild yos aak is ander to define the initial objectlvet of a sompary? List Two gaticios. [2 marks] (iii) Suppose yow are the desipher. Your view ant marayer's view abeut the dasa are (b) Deseribe the databese recovery jrocuse theuch bee ase of aclinas arite and arhethough sectriques. (f marta) (a) A distributed database management system (DDBMS) of a university is shown in Figure 5. These operations include remote request, remote transactions, distributed transactions and distributed requests are needed to execute the following SQL commands. Based on the given SQL command, specify the minimum types of operation the database must support and justify your answer. (i) At site C: SELECT ' FROM RESULT WHERE RES_CGPA > 3.66; [1.5 marks] (ii) At site A: BEGIN WORK: UPDATE STU_A SET STU_CONTACT 20116112212; INSERT INTO INVOICE (INV_NO, STU_ID, INV_DATE, INV_TOTAL) VALUES('W321112', '1001100991', '2018-09-18', 2500); COMMIT WORK; [1.5 marks] (iii) At site C : SELECT * FROM STUDENT WHERE STU_NAME LIKE 'MW'; [1.5 marks] (iv) At site B: SELECT STU_NAME, INV_TOTAL FROM STUDENT, INVOICE WHERE INV_TOTAL > 1000 AND STUDENT.STU_ID = INVOICE.STU_ID; [1.5 marks] (b) The relational database has faced challenges due to the emergence of object-oriented databases and the development of data warchouse. The latest challenge is big data. Answer the following questions about big data. (i) What is big data? (ii) Why big data is a challenge to relational database? [2 marks] [2 marks] End of Page PYO 5/5 Naim is writing Structured Query Language (SQL) to implement the entity relationship diagram (ERD) shown in Figure 4. Suppose you are Naim. Solve the following questions with appropriate SQL. The date format of the Database Management System (DBMS) is YYYY-MM-DD. (a) Create a table Product with appropriate data types. NO null value is allowed for all attributes. [3 marks] (b) List each order number with date, customer name and customer number. Sort the record by date. [2.5 marks] (c) Naim has designed the following SQL to fulfil the request by his client. He needs to list the total price of each client by order number. Below is the sample output: Here is the SQL written by Naim: SELECT OrderNo, Date, CustomerNo, CustomerName, SUM(UnitPrice : Quantity) AS 'TotalPrice' FROM PRODUCT AS p, ORDER_LINE AS ordl, ORDER AS ord, WHERE p.ProductNo = ordl.ProductNo AND ordl.OrderNo = ord.OrderNo AND CustomerNo = CustNo; Check the correctness of the query and justify your answer. [1.5 marks] (d) Assume table OrderLine has created. Change ProductNo to foreign key. This foreign key will restrict the deiete option and enforce the update. [3 marks] (a) Questions below are related to database development life cycle. (i) During database initial study, what questions would you ask in order to define the initial objectives of a company? List TWO questions. [2 marks] (ii) Suppose you are the designer. Your view and manager's view about the data are different. Please provide two examples view of each party. [3 marks] (b) Describe the database recovery process through the use of deferred-write and writethrough techniques. [5 marks]

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Students also viewed these Databases questions

Question

10. What is meant by the term management theory jungle?

Answered: 1 week ago

Question

What is P{T1 < T1 < T2}?

Answered: 1 week ago