database administration, design, modeling , ERD and development

Title of project:

Database Management System

Learning Outcomes and assessment criteria covered

CLO2: Describe the relational database model.

CLO3: Create the ER model.

CLO4: Apply the normalization of database tables.

CLO5: Develop specific skills in database design and implementation using a commercial relational DBMS.

Introduction to Project

You and your group members are part of a consultancy company that specialises in the provision of database applications. The Director of Syzygy Inc. has recently approached your company to undertake a project to design and partially implement a database management system for the company.

DATABASE REQUIREMENTS

Consider a real retail receipt from ANY hypermarket where you shop for groceries.

Do the following tasks based on this relational database:

a. Perform Normalisation to identify the appropriate tables and attributes for the existing database. Provide the Functional Dependency Diagrams and show the steps from UNF to 3NF/BCNF. Please make sure the primary keys and foreign keys are identified in your relational schema.

b. Create a data dictionary for the finalized table.

c. Create ALL tables using DDL. (Choose a proper data types for each attributes)

d. Insert a suitable numbers of records into each table. (You may use GUI/DDL). Make sure the records inserted can gives an output for the queries in (e) and (d).

e. Construct the following SQL queries:

I. Find the total number of sales transactions at the chosen hypermarket in the month of January 2016.

II. Find the products that are out of stock that must be reordered.

d. Construct 5 more queries with sql codes that use complex or nested queries. The queries should involve calculation, set condition and joining of multiple tables.

e. Create the relationship between tables in the DBMS/ draw ERD using Chen Notation

f. Create a customized switchboard, form and report and provide the screen shot of each in the report

You should hand-in a printout of:

1. Each table which shows the table name and the fields. (Design View)

2. Each table with its sample records (Data sheet View)

3. The capture screen of switchboard, form, report and relationship

4. The capture screen of each query and its outputs.

5. Prepare presentation slides with the items 1 to 4 with lesson learned

6. Demonstrate the system prototype during the given date (will update in the class)

more about is attached

Data Entry Data Processing Data Warehousing Database Administration Database Development

Project ID: #9226460

About the project