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 LOs. 

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 
2. Identify functional dependencies, referential integrity, data integrity and  security requirements; Further integrate and merge physical design by applying  normalization techniques. 
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. 

Please note: Assignment 2 is based on Assignment 1.

Assignment Requirements and Deliverables:  

Part B – 10% (Due in week 9)

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

        a. 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 

            one 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 Statements 

          a. List the branches (ID) of MA that have in stock at least 5 bottles of  Penfold Grange 2010. 

           b. 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.

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