Assignment Progress Monitoring

This assignment has four sections to it. To monitor your progress, you will be asked to present sections of your work to your tutor during upcoming lab classes. This progress monitoring is not formally marked but is used as a means to monitor how you are progressing through the assignment and provide feedback. Also, this encourages students to achieve milestones as they work on their assignment and not leave it to the last moment. The entire assignment will be marked after the due date.

Following are the sections and when they are due for progress monitoring:

Section 1: Conceptual DB Design, EER to Relational Model Mapping & Normalisation  (5 marks)

In this section, you will use the Assignment 1 feedback to revise the data requirements, transaction requirements, business rules and EER model.

Next, the EER model needs to be mapped to a relational schema and normalised.

The relational model needs to be documented in DBDL format. Sample DBDL format is given below:
 SBN (id, number, itemNo)
 Primary Key id
 Alternate Key number
 Foreign Key itemNo references Book(itemNo)
 ON UPDATE CASCADE, ON DELETE CASCADEid

DBDL format is provided in your text – Database Systems : A Practical Approach to Design, Implementation and Management – 5th Edition.

Section 2: Implementation – Database Script (5 marks)

Create a T-SQL script for the database design in section 1. You will create a database with all the necessary tables and constraints: primary key, foreign key, not null, unique and check constraints. The database must be populated with sufficient and meaningful records for evaluation purposes.

Section 3: Stored Procedure (10 marks)

Implement the following stored procedure. Ensure that the stored procedure is tested with appropriate sample data. Test cases should be saved in a separate test script.

Section 4: Business Rule (5 marks)

Business Rule: Order Satisfiability Before an order can be taken, it is important to verify that the order can be satisfied with the available ingredients in the store. If the ingredients available are insufficient to fulfill the order, an appropriate error message needs to be generated and the order cancelled.

Ensure that the above business rule is enforced in the database. You need to generate appropriate error messages if an attempt to violate the constraint is attempted.

Notes: It is especially important for sections 2-4 that the T-SQL scripts are documented - commented and indented for clear understanding by the marker. Where complex pieces of code are written, documentation in pseudo-code is encouraged.

The test scripts also need to be well documented. e.g. a heading comment firstly to state whether the expected result is a Success or Fail test case and a description. Please ensure that you test appropriately with different inputs (valid and invalid).

Ensure that appropriate sample data is used to test your database comprehensively. Also ensure that appropriate error messages are generated.

Submission Requirements

Both a softcopy and hardcopy must be submitted for the assignment

Softcopy

The following items need to be submitted to Blackboard via Assessment / ASSIGNMENT 2 / Assignment 2 Submission (will be checked by Turnitin). Include all files in a single zip folder identified by 4 sections: A2, your first name, your surname and your student number, e.g. A3SimonLee1234567.zip Database documentation – Data Requirements, EER Model, Data Dictionary, Normalised Relational Schema. Note that you need to specify to which normal form that the schema is normalised. If you found any functional dependencies that violated the normal form, illustrate the normalization process. SQL Script files The SQL script files to be included are listed below.

Hardcopy

Hardcopy is to be submitted to your lecturer in person in lab class. The following items will be required for submission: A signed Individual Assessment cover sheet Printout of database documentation (Requirements, Revised EER Model, Normalised Relational Model) Printout of all SQL Scripts

Demonstration

Each student must demonstrate his or her assignment. Assignment 2 demonstrations will be held during the lab session on the due date. Failure to attend the demonstration can result in a zero mark for the assignment.

To Continue Click Here > INFO6002 Assignment 2 Database Implementation Database Management2 Callaghan.pdf