Assessment details for ALL students Assessment item 2—Assignment 2 Due date: Friday of Week 10 (May 20, 2016 - 11:45 pm) Weighting: 30% Length: N/A Objectives • To develop a conceptual data model diagram • To perform logical design and physical design • To implement a database Introduction In this assignment, you need to create a suitable database in MS Access for the given case study. You need to do the following: • Draw an Entity Relationship (ER) diagram • Provide answers to the given questions related to ERD • Perform logical and physical design • Create Database in MS-Access Case Study CQU shopping complex (CQSC) is a multi storey building in which many shops are operating their businesses to general public. Only after executing a lease agreement for a shop-premise in CQSC, a business owner can start his/her business (CQSC-SHOP) in that shop-premise. Each CQSC-SHOP is described by its name, type, premise Id and floor level in CQSC. As part of your assignment, you are expected to assume the details of business owners, shop-premises and lease agreements. CQSC creates many shopping-catalogues mainly for promoting various sale-promotions that are offered by CQSC-SHOPs. Each of those catalogues is identified by a unique identification number and a release date. These catalogues mainly provide the details of only some (and not all) of the CQSC-SHOPs operating in its complex and may contain many advertisements related to different products or services that are offered by them. You may also note that a CQSC-SHOP may list the details of only some (and not all) of its products/services in those advertisements. Each of those advertisements can list a number of products/services and optionally discount-sale, if any, including the duration of the discount-sale period. The discount offered by a CQSC-SHOP will definitely have its own limitations. As part of your assignment, you are expected to assume the details of those limitations.   1 Draw an appropriate ER diagram Use the symbols as prescribed in your course-textbook to draw the ER diagram for the above case study. 2 Answer the following questions Your answers have to be relevant to your ER diagram for the given case study only. a. List your assumptions (relevant to data and relationship) related to any three of the following: • Business owner • Shop-premise • Lease agreement • Discount offered b. Sometimes, a CQSC-SHOP may be moved from one shop-premise to another one in CQSC. How can it be shown in your ER data modelling? 3 Create Logical Design: Map your Entity Relationship diagram into relations and make sure that all the relations are in BCNF. Provide all the relations in the following format: Customer (CustomerId, customerName, Street, Suburb, State, PostCode, Email) Invoice (InvoiceNumber, invoiceDate, Total, CustomerId) foreign key (CustomerId) references Customer(CustomerId) 4 Provide Physical Design As part of the physical design, provide the column specifications for any one of the relations/tables that you have created. Column specifications should be presented in a tabular format showing the details such as attribute name, data type/length, key, required, default value and validation rule(s) if any. 5 Implementation Create all tables and their relationships that are relevant to your case study in a Microsoft Access database. All tables should contain their respective columns. All the relationships should have appropriate referential integrity and cascade options applied. The properties for all the columns of the table, that you have selected in the above physical design section, should have all the column specifications properly applied. You need not create any form, query or report. You need not insert any records in the tables. How and what to submit: You must make an electronic submission for this assignment using the appropriate assignment submission link in the course web site. Your submission must be a single ZIP file which contains your assignment work in the following two files:   A Microsoft Word document containing 1. Entity Relationship Diagram (ERD) 2. Answers to the given two questions (2a and 2b) 3. All the relations (after the logical design) 4. Column specifications for any one table only A Microsoft Access database containing 5. All tables and their relationships -------------------------------------------------------------------------------------------------------------- Marking Criteria: Item Maximum Marks Entities have been identified and drawn correctly. 5 Provided appropriate attributes including identifying attribute(s) for each entity. 5 Drawn correct relationships; used proper symbols and shown min and max cardinalities. 5 Provided answers for 2a (3 marks) and 2b (1 mark). 4 Correct BCNF Relations including foreign key references. 3 Column Specifications for the selected Table under section 4 (Provide Physical Design). 2 Database with Tables (2 marks) and Relationships (2 marks). Correct data type and properties (2 marks) for all columns of the selected table under section 4 (Provide Physical Design). 6 Penalty due to plagiarism/copying Late Penalty: Less 5% of available marks per calendar day. Total 30