Reference no: EM133526527

Database Systems

Objectives
This assessment item relates to the unit learning outcomes as in the unit descriptor. This assessment is designed to improve students' skills to analyze organization database requirements, normalize the database and use data manipulation language to create, update and manage database. Furthermore, their research skill will be analyzed and application on the given case study.

This assessment covers the following

Learning outcome 1: Derive a physical design from the logical design taking into account application, hardware, operating system, and data communications networks requirements; further use of data manipulation language to query, update, and manage a database

Learning outcome 2: Identify functional dependencies, referential integrity, data integrity and security requirements; Further integrate and merge physical design by applying normalization techniques.

Learning outcome 3: Design and build a database system using the knowledge acquired in the unit as well as through further research on recent trends to demonstrate competence in various advanced tasks with regard to modelling, designing, and implementing a DBMS including Data warehousing, Data Management, DB Security.

Part B 
Submit a single plain text file with filename as "studentid_studentname_AL_SQL" containing all SQL implementation. Your SQL queries must work on MS SQL Server and be able to be demonstrated.
SQL code required (Use MS SQL Server):

1. Create a database and CREATE TABLE statements for all tables in your ERD (Assignment 1) including primary and foreign keys.

2. INSERT INTO statements for populating the database
i. Insert five rows of (made-up) data into each table. Make sure that the data you enter in these tables should be sufficient to return at least bone row for each query in Task 3. AL should hold at least 5 bottles of Penfold Grange 2010 in some branch or other.

3. Select Statementsi. List the branches (ID) of MA that have in stock at least 5 bottles of Penfold Grange 2010.ii. SELECT statement to generate a list of all email addresses of members whose card will expire in the month after the coming month. Thus, for instance, if the query is run in November 2121, it will list the emails of all members whose membership will expire in January 2122. The emails should be ordered by Branch ID, then by expiry date, and then by the email address, all in ascending order.

Research and Discussion
Submitted as a MS Word or PDF Document

4. Consider the following relation schema as the join of a few tables from Assignment 1 ERD

Abnormal_Rel ( ProductID, BranchID, campaignID, MemberID, ProductType, PackageType, YearProduced, Price, Brand, StockLevel, CampaignStartDate, CampaignEndDate, FirstName, LastName, eMail, MembershipLevel, MemberExpDate, Discount
)

Determine for UPDATE anomaly whether or not the relation Abnormal_Rel is susceptible to that anomaly. Support your determination with adequate explanation and a small example.

5. Normalize/decompose the relation schema Abnormal_Rel until you get relations that are in 3NF. Use appropriate illustration to aid the understanding of your work.