Reference no: EM132771592

Question 1

Draw an Entity Relationship Diagram (ERD) for the following case study:

Management Book Association (MBA) is engaged in a wide range of publishing, marketing and distribution activities in Singapore. These are in the areas of educational textbooks, children's books, general and trade books, academic and scholarly publications, reference materials and some electronic publications. MBA also collaborates with the local university's professors to conduct industry seminars and workshops for professionals.

MBA wishes to produce course books on the various seminars and workshops that it now conducts. The books are written by professors who specialises in one particular subject. Two or more professors may specialise in a particular subject. MBA employs professional editors who may not be a specialist in a particular area. Each editor may edit one or more books at a time. A professor may write more than one book at a time. When writing a particular book, the professor works with one editor. If the professor is working on a second book at the same time, he has to work with another editor on the second book.

Question 2

This question has TWO (2) parts, part A relates to normalization and part B relates to SQL.

Kaplin Art is a digital art shop focused on buying the best of computer art and design. Kaplin Art is developing a new website to sell their arts. The website will list all the artists and their arts. Customers can buy from Kaplin Art and later sells it back to Kaplin Art, who then will relist them on their webstore for other interested customers. Kaplin Art do not allow customer to sell to other customer on their website. One of the requirements of the new webstore is to maintain a list of their customers and what they have purchased. Figure 2 show the report layout.

Figure 2. Customer Purchase History Report

Part A: (Please clearly state all assumptions and ensure that you have sufficient annotations).

Please complete the following FOUR (4) parts:

i) Based on Figure 2, write down the unnormalized form.

ii) Identify the repeating group of attributes and transform part (i) into tables that are in 1st Normal form. (Explain the rationale for your decision).

iii) Identify any partial dependencies in part (ii) and transform into tables that are in 2nd Normal Form. (Explain the rationale for your decision).

iv) Identify any transitive dependencies in part (iii) and transform into tables that are in 3rd Normal Form. (Explain the rationale for your decision).

Part B: (Please clearly state all assumptions and ensure that you have included the screen capture for each part - marks will be deducted if you do not provide evidence of execution)

Using any dialect of SQL, please complete the following SEVEN (7) parts:

i) Draw an entity-relationship diagram of the identified tables in Part A.

ii) Using the CREATE TABLE in any dialect of SQL, show the corresponding SQL tables. Indicate the primary key, foreign keys, column names, constraints etc.

iii) Insert (at least) 10 sample rows for each identified table.

iv) List the names and phone numbers of all customers who have a credit limit > $10,000 in alphabetical order.

v) Find the average purchase price of all the purchases for a particular customer.
vi) List all the items with a price above the average price for a particular customer.

vii) List the customer and art title that has been sold at least 2 times.

Attachment:- case study.rar