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

Marking Guide: Case Unacceptable Acceptable Good Excellent
1. Create table statements (Weightage 1 Mark) Incorrect syntax, tables are not in line with Assignment 1 ERD. Partially correct syntax, tables are somewhat in line with Assignment 1 ERD. Syntactically correct and most tables are in line with Assignment 1 ERD. Code is correct and all tables are in line with Assignment 1 ERD.
2. Insert Into statements (Weightage 1 Mark) Incorrect syntax Partially correct syntax and records are partially inserted as per the requirements Syntactically correct and most of the records are inserted as per the requirements. Code is correct and records are inserted as per the requirements
3. Select statements (Weightage 3.5 Marks)

3.1 Select statement to list the branches (ID) of MA that have in stock at least 5 bottles of Penfold Grange 2010. (1.5 Marks)

Incorrect syntax Partially correct syntax and gives output somewhat near to requirements. Syntactically correct and gives output very near to requirements. Code is correct and used JOIN, etc. and gives required output
3.2 SELECT statement to generate a list of all email addresses of members whose card will expire in the month after the coming month. (2 Marks Incorrect syntax Partially correct syntax and gives output somewhat near to requirements Syntactically correct and gives output very near to requirements Code is correct and used JOIN, etc. and gives required output
4. Anomalies (Weightage 1 Mark)

4.1 Normalization in relation to the case study (Weightage 3.5 Marks)

Does not adequately identify anomalies. Identify a few of the anomalies and remove those anomalies from the tables. Correctly identify most of the possible anomalies and remove those anomalies from the tables. Correctly identify all of the possible anomalies and remove those anomalies from the tables.
4.2 discuss how 1NF was achieved for each entity. (0.5 Mark) Does not adequately identify repeating group of columns and not transform unnormalised tables into 1NF tables. Identify a few of the repeating group of columns and correctly transform a few of the unnormalised tables into 1NF tables. Identify most of the repeating group of columns and correctly transform most of the unnormalised tables into 1NF tables. Identify all of the repeating group of columns in each table and correctly transform all of the unnormalised tables into 1NF tables
4.3 discuss how 2NF was achieved for each entity. (2 Marks) Does not adequately identify partial dependencies and not transform 1NF tables into 2NF tables. Identify a few of the partial dependencies and correctly transform a few of the 1NF tables into 2NF tables. Identify a few of the partial dependencies and correctly transform a few of the 1NF tables into 2NF tables. Identify all of the partial dependencies in each table and correctly transform all of the 1NF tables into 2NF tables.
4.4 discuss how 3NF was achieved for each entity. (1 Mark) Does not adequately identify transitive dependencies and not transform 2NF tables into 3NF tables. Identify a few of the transitive dependencies and correctly transform a few of the 2NF tables into 3NF tables Identify most of the transitive dependencies and correctly transform most of the 2NF tables into 3NF tables. Identify all of the transitive dependencies and correctly transform all of the 2NF tables into 3NF tables.