Reference no: EM132978446
ITECH2004 Data Modelling
Assignment - SQL Database Design and Implementation
Learning Outcome 1: Design a relational database for a provided scenario utilizing tools and techniques including ER diagrams, relation models and normalization
Learning Outcome 2: 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.
Application of Knowledge and Skills:
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
An online retailer, Appliances To Your Door sells whitegoods e.g. refrigerators, washing machines and ovens and other electrical appliances over the phone and the World Wide Web. They need a system that handles the delivery of purchased goods to customers. They need to understand the data storage requirements for this system.
Appliances To Your Door have distribution warehouses in each of the states of Australia (the South Australia warehouse looks after the Northern Territory and the NSW warehouse looks after the Australian Capital Territory). The warehouse is usually located in an industrial sector on the outskirts of the state capital. For example the warehouse for Victoria is located at an address in Laverton North. Each warehouse has a unique id, one or more managers, address (two address lines, suburb and state (postcode is obtained from these), email address, phone number and other information is also kept about capacity space (in square metres), occupied space (in square metres), number of loading bays, number of access points/doors and a general description for any other interesting information. Information is additionally kept about the manager/s e.g. title, highest qualification obtained and date of that qualification.
As well as managers, the warehouses also have other employees - workers, drivers and jockeys (assistants). The workers pack vans for delivery of whitegoods and unload and store warehouse deliveries. All workers must be a licenced vehicle driver (the licence number and expiry date are kept on file) and also have a licence to drive forklifts and the forklift licence number and expiry date are also kept on file. All drivers have a driver's licence and a record of any endorsement to drive certain vehicles. As well as their driver's licence number and expiry date, the highest endorsement level and the endorsement expiry date are kept on file. A driver is able to drive a delivery vehicle with a Gross Vehicle Mass (GVM) that is equal to or less than his endorsement level. All jockeys have certificate qualifications that allow them to correctly install appliances. The certificate title and year awarded is kept on file for jockeys. Jockeys assist the driver in delivering and installing the delivered appliance. Sometimes jockeys also drive, but only in emergencies. Their driver's licence number and expiry date must also be kept on file. For all employees, a record is kept of their employee id, first name, last name, contact phone, contact email, start date, termination date and as noted above, driver's licence number and expiry date.
Each warehouse has a fleet of transport vehicles. These can range from 4.5 tonne trucks to small 1 tonne vans. All vehicles are identified by their registration number and information is also kept about their type, seating capacity, carrying capacity (the tonnage GVM already mentioned e.g. 4.5 tonnes), kerb mass/weight (the tare mass with a full tank of petrol i.e. the weight of an empty vehicle ready to be loaded), load space in cubic metres, maximum load area height, maximum load area width, maximum load area depth and status (e.g. "Being Loaded", "Ready for Delivery", "On Delivery", "In Service").
To make a purchase through AppliancesToYourDoor, it is necessary to sign up and create a customer account. Information kept about a customer includes customer id, first name, last name, phone, email and address (2 lines of address, suburb and state (postcode is obtained from these). For a delivery to be made a customer must have a paid invoice for the goods in question. An invoice is made up of a header record containing invoice id, date, customer id and payment status (T/F). Each line item of the invoice contains a product id, product description and sold price. Other information kept on the product includes product type code (e.g. "RF" for refrigerator, "WM" for washing machine, "TV" for television), unpacked and packed dimensions (height, width, depth) and packed and unpacked weight (in tonnes). After a customer has ordered and successfully paid for their item/s, their invoice is complete and one or more delivery requests are created. The delivery request is made up of one or more of the items on the invoice. For example, Mr. Smith may have paid for 2 refrigerators and a washing machine. These would be recorded as three separate line items on the invoice. He may want one refrigerator to be delivered to his mother who lives at an address in Sydney, and the other two items, refrigerator and washing machine delivered to his home address in Melbourne. The refrigerator delivery to his mother would be allocated to and handled by the Sydney warehouse and the Melbourne warehouse would be allocated and handle the delivery to his address. The delivery request has a unique id, request date, requested delivery date, actual delivery date, delivery address (2 lines of address, suburb and state (postcode is obtained from these)), contact name, contact phone number, delivery warehouse id and delivery instructions. For the purposes of obtaining postcodes, a record is kept of the postcode attached to the suburb and state. Additionally, for obtaining road distances between these locations, a record is kept of the distances between each suburb and state combination so that the road distance from the warehouse to the delivery location can be obtained and the road distance between a suburb and state combination and another suburb and state combination can also be obtained.
At regular intervals, a warehouse manager generates delivery schedules. In order to generate a delivery schedule she first selects a date to filter the outstanding requests for the warehouse she manages. The outstanding requests selected may be past due requests, including those deliveries that have been unsuccessfully attempted. She is then presented 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). This information is presented in ascending order based on requested delivery date and distance from the delivery warehouse to the delivery address (looked up on the record of distances between each suburb and state combination). She then decides to create a schedule for a particular delivery type. At present, there are five types of deliveries - "Suburban", "Regional Inner", "Regional Outer", "Regional Remote" and "Regional Extreme". These types have a maximum distance attribute e.g. currently for "Suburban" it is 100 kilometres (km), for "Regional Inner" it is 200 km, for "Regional Outer" it is 500 km, for "Regional Remote" it is 1500 km and for "Regional Extreme" it is 2500 km. She also selects an available vehicle and assigns a driver and a jockey. She then starts selecting from the list to create the particular schedule. The total of the requests assigned to the schedule for the vehicle must meet the following rules:
• No single requested item can exceed the maximum load area height, width or depth of the vehicle;
• The total occupying area of the requested items (the sum of each item's packed width multiplied by packed depth) must not exceed the total load area (maximum load area width multiplied by maximum load area depth) of the vehicle;
• The total distance to be travelled on the schedule must not exceed the maximum distance attribute for the type of delivery schedule selected (NB: after the first request is selected any further requests are added to the total distance travelled by looking up the from suburb and state and to suburb and state records to obtain the distance from the previous schedule item's location (suburb and state) to the next chosen delivery location (suburb and state). For example, imagine a "Regional Inner" schedule is being developed and the requested deliveries for the date include (in distance from warehouse ascending order): refrigerator delivery to Tarneit (11.4 km road distance from Laverton North), television delivery to Taylors Lakes(19.1km), television delivery to Burwood (35.4 km), washing machine delivery to Frankston (71.2 km), refrigerator delivery to Ballarat East (97.1 km), washing machine delivery to Lal Lal (98.1 km), microwave delivery to Mt. Helen (103 km), television delivery to Miners Rest (110 km), refrigerator delivery to Seymour (115 km), two air conditioners to Smythesdale (117 km), refrigerator to Cardigan Village (118 km) and freezer to Warragul (121 km). The scheduler decides to choose the locations to maximize the total cumulative distance to at or just below the maximum distance attribute value of the Regional Inner type (200 km). She might choose for example, the refrigerator delivery to Ballarat East (97.1 km cumulative distance), the microwave to Mt Helen (97.1 + 8.4 (road distance between Ballarat East and Mt. Helen) = 105.5 km cumulative distance), the television to Miners Rest (105.5 + 23.5 = 129.0 km cumulative distance), the refrigerator to Cardigan Village (129.0 + 13.7 = 142.7 km cumulative distance), the two air conditioners to Smythesdale (142.7 + 15.3 = 158.0 km cumulative distance) and the washing machine to Lal Lal (158.0 + 37.4 = 195.4 km). Other combinations could be tried e.g. going from Mt. Helen to Lal Lal and then Smythesdale, Cardigan Village and Miners Rest to finish. Eventually a schedule is created with the delivery order recorded).
Once the schedule has been defined, the vehicle is packed according to that schedule and the driver and jockey attempt to deliver the goods. Each time a delivery is attempted a delivery attempt record is created with a date, success flag and a comment. When a delivery is successful, the attempt record is created with a ‘T' success flag value and a comment and the actual delivery date on the delivery request is updated. Sometimes more than one attempt is made in the execution of a delivery schedule. Each time a delivery is unsuccessful, a delivery attempt record is created with the date, success flag set to ‘F' and a comment. Sometimes it is not possible to deliver appliances so they are returned to the warehouse at the end of the schedule delivery run.
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:- Data Modelling.rar