Reference no: EM132977611

Assignment - SQL Database Design and Implementation

Learning Outcome 1: Interpretation of business rules from a case study;

Learning Outcome 2: Conceptual data modelling through the creation of an Entity Relationship(ER) model;

Learning Outcome 3: Application of DDL and DML components of SQL to:

a. create and populate a relational database; and
b. query the created relational database.

Design a relational database for a provided scenario utilizing tools and techniques including ER diagrams, relation models and normalization

Describe relational algebra and its relationship to Structured Query Language (SQL).

S1. Interpret entity-relationship diagrams to implement a relational database.

S2. Demonstrate skills in designing and building a database application using a commercially available database management system development tool.

S3. Use a query language for data manipulation.

A1. Design and implement a relational database using a database management system.

A2. Utilise a query language tools and techniques to obtain data and information from a database.

Overview
Students are expected to read the provided system description and then interpret that description to create an ER model of that system.

They are then expected to provide an implementation of the ER model in the form of the DDL to create the required tables, attributes and relationships.

Students are then required to provide the DML to insert sufficient information into the database to answer a set of queries.

Finally, students are expected to provide the DML to interrogate the database to answer the queries posed. They should also provide proof of the running of those queries by providing images of the output obtained.

It is a requirement of this assignment that students use Postgres for the database components.

Case Study - Appliance Deliveries System

Detailed Requirements
This assignment is an individual assignment. It is a requirement of this assignment that students use Postgres for the database components.
Students should submit a report that follows the format of a business/professional report and contain, at a minimum, a Title Page, Table of Contents, Executive Summary and References (if cited) and the following content:

1. An ER model of the case study system. This should conform to third normal form. Students should be aware there are a number of disjoint subtype entities and there is at least one example of a unary relationship that needs to be included. Students are able to use any drawing package to present the ER diagram but the diagram should use the Crows foot notation and conform to the standards identified in Coronel and Morris (2018). These include that entities are shown in a rectangle with name of entity in grey at top separated from two columns below with PK, FKn identifiers, where appropriate in the first column and attributes in second column. Primary key attributes to be separated from other attributes by a line across the rectangle. All entity and attribute names to be in upper case. All relationships should be labelled and identified as weak (non- identifying)/strong (identifying) ones. All connectivity, participation and cardinalities (if there are specific limits) should be shown. For an example ER diagram see Figures 4.31 and 4.35 of Coronel and Morris (2018).

2. A screen shot of the pgAdmin 4 GUI showing the creation of a database with the name ITECH2004_yourStudentID_Delivery_System.

3. The DDL statements required to create an implementation of the conceptual data model above. Students must use Postgres and their created database to create these tables, attributes and relationships. Transaction and Commit statements should be included in the DDL. They should include DROP TABLE commands where necessary and must show the correct order of creation. Appropriate constraints must be created. Students must follow the naming conventions i.e. uppercase for keywords, lowercase names for tables and attributes with an underscore between words and new line for each clause. Students should use the default schema i.e. there is no need to create one.

4. DML statements to insert sufficient data into the database to correctly answer a set of queries. Transaction and Commit statements should be included in the DML.

5. DML statements and screen shots of the correct operation of the following queries. Students should ensure that they follow conventions in their writing of SQL - uppercase for keywords, lower case for table and column names and new line for each clause:

a. Select the name details of all employees with the surname starting with "S". Order by the surname.
b. Select the name and licence details of all employees whose licence is going to expire before the end of this year i.e. 31st December, 2021.

c. Select all details of vehicles where the calculated load capacity (name this column ‘calculated_load_capacity') is between 1 and 3 tonnes.
d. Select the total unoccupied space across all warehouses i.e. one row with one value is returned.

e. Select a count of all products and the maximum price of those products, grouped by product type having a maximum product price greater than $1000.00.
f. Produce the rows of a delivery schedule with all request details including information about the customer, the requested delivery item and information about the product including description, packed dimensions (height, width, depth) and packed weight (in tonnes). Include the distance from the delivery warehouse to the delivery address and order by that distance.

g. Select all products, displaying product id and description and associated delivery request details (unique id, request date, requested delivery date, actual delivery date) of that product. If there are no delivery requests for that product, still display the product with NULL values in the delivery request details columns.

h. Select a list of all customers, showing all customer details, of those customers that have had more than one delivery request.

Attachment:- SQL Database Design.rar