ITECH2004 Assignment 1 – SQL Database Design and Implementation

Purpose and Learning Outcomes 

Purpose 

The purpose of the assignment is to provide students with the opportunity to apply knowledge and skills developed  during the semester with particular reference to: 

1. Interpretation of business rules from a case study; 
2. Conceptual data modelling through the creation of an Entity Relationship(ER) model; 
3. Application of DDL and DML components of SQL to: 
4. create and populate a relational database; and 
5. query the created relational database. 

Learning Outcomes 

The learning outcomes directly assessed are: 

Knowledge: 

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

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

Skills: 

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. 

Timelines and Expectations 

Marks: Assignment will be assessed based on a mark out of 60 

The following information is a summary from your Course Description: 

Percentage Value of Task: 30% of the course marks 

Due: Friday, September 3rd, 2021 at 4:00pm 

Minimum time expectation: 25 hours 

Students are required to complete the assignment individually.  

Students are expected to submit the required report and details (see below) to the submission box in their Moodle  shell. 

Assignment Requirements 

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. The submission must be presented in the format of a professional report. Further information is given in the Detailed 

Case Study – Appliance Deliveries System 

An online retailer, AppliancesToYourDoor 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.  

AppliancesToYourDoor 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: 
6. Select the name details of all employees with the surname starting with “S”. Order by the surname.

2021. 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. 
2022. Select all details of vehicles where the calculated load capacity (name this column  ‘calculated_load_capacity’) is between 1 and 3 tonnes. 
2023. Select the total unoccupied space across all warehouses i.e. one row with one value is returned. 
2024. 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. 
2025. 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. 
2026. 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. 
2027. Select a list of all customers, showing all customer details, of those customers that have had more  than one delivery request.