You have been assigned as a database developer to create a database design for the enrolment system at Discover College. You have also been tasked to implement the Discover College Purchasing Database. • Task 1: Propose the design of a relationa
9 min read
Assessment Brief
|
Module title: |
Introduction |
|
Module code: |
COM4015 |
|
Assignment title: |
COM4015 |
|
Assignment format: |
Practical |
|
Word/time limit: |
3000 |
|
File type |
Docx/pdf |
|
Percentage of final |
This |
|
Submission deadline |
See module iLearn page for |
|
Grade release |
You |
|
|
|
Useful terms:
|
Learning outcomes (LOs) |
The skills and knowledge that you should be able to show in |
|
Marking Matrix |
A set of rules or guidelines used to grade or assess work. |
Task summary:
You have been assigned as a database developer to create a database design for the enrolment system at Discover College. You have also been tasked to implement the Discover College Purchasing Database.
• Task 1: Propose the design of a relational database for Discover College Enrolment system.
• Task 2: Implement a database for Discover College Purchasing System.
• Task 3: Test the implementation of the Discover College Purchasing System.
Assignment instructions:
Task 1: Propose the design of a relational database for Discover College Enrolment system
Discover college has decided to implement a database system to manage its course enrolment process.
The college offers a range of courses and employs lecturers to teach them. Students can enrol on multiple courses, and each course may have many students enrolled.
Each course belongs to a department and is delivered in a specific classroom. Lecturers may teach more than one course, but each course has one lead lecturer responsible for its delivery.
The college also wishes to record the date a student enrolled and their current enrolment process data requirements (e.g., Active, Withdrawn, Completed).
To improve their enrolment operations the principal would like you design a database which will streamline the enrolment process at Discover College.
A). Entity Relationship Diagram (ERD)
Design a Physical Model Entity Relationship Diagram (ERD) using Crows Foot Notation for the scenario above.
Your ERD must include:
• Entities
• Appropriate attributes for each entity.
• Primary Keys (PKs).
• Foreign Keys (FKs).
• Data types for all attributes.
• Constraints for attributes where appropriate
• Relationship cardinality (e.g., one-to-one, one-to-many).
• Relationship modality (mandatory or optional participation).
Your diagram should clearly show all relationships between entities. Please include any assumptions and reasoning for your design. This diagram does not need to be normalised to 3NF.
B) Normalisation
Write a commentary of approximately 500 words explaining how your database design could be improved using the rules of normalisation. You should include specific examples from your own design to support your explanations.
Your discussion should:
• Explain the purpose of database normalisation.
• Refer to First Normal Form (1NF), Second Normal Form (2NF) and Third Normal Form (3NF). Identify examples from your own ERD where redundancy could occur.
• Explain how repeating groups, partial dependencies and transitive dependencies could be removed in your ERD.
Task 1: (900 Words equivalent )
(30 marks)
(LOs: 2)
Task 2: Implement a database for Discover College Purchasing System.
Discover college purchases supplies and equipment from a range of suppliers to support teaching, administration and maintenance activities.
The college has several departments, including Computing, Business, Engineering and Student Services. Department managers can request items such as stationery, laptops, projectors, office furniture and cleaning supplies.
When a department requires items, a purchase order is created and sent to a supplier. Each purchase order may contain multiple items, and suppliers may receive many purchase orders over time.
A). Based on Figure 1. Discover College Purchase system physical model entity-relationship diagram, create the database using appropriate SQL in the database account provided by Arden University. Please include text copies of your CREATE TABLE statements along with screenshots of table creation. Please ensure screenshots contain your username.
Figure 1. Discover College Purchase System physical model entity-relationship diagram
B). Once the database is created, insert the following data:
• 6 entries for the Supplier table
• 6 Entries for the Departments table
• 12 Products in the Products table using the sample data in Table 1.
• Insert 6 entries in the Purchase orders table with at least 3 OrderLine records for each purchase order.
• 6 entries for the GoodsReceipt table
Please include meaningful records for all tables. The given number of records are the minimum requirements for the database. You are allowed to add more data as required. Larger amounts of relevant and accurate data will receive higher marks. Please consult marking matrix. Please include text copies of your INSERT statements along with screenshots showing data insertion. Please ensure screenshots contain your username.
Table 1: Discover Products Table
|
Product Name |
Unit Price |
Stock Quantity |
Product Category |
|
A4 Printer Paper (500 Sheets) |
5.99 |
120 |
Stationery |
|
Blue Ballpoint Pens 10) |
3.49 |
200 |
Stationery |
|
Whiteboard Markers (Pack of 4) |
4.99 |
75 |
Stationery |
|
Lever |
2.25 |
90 |
Stationery |
|
Stapler |
8.50 |
40 |
Stationery |
|
Dell |
749.99 |
15 |
IT Equipment |
|
24-inch |
129.99 |
25 |
IT Equipment |
|
Wireless |
24.99 |
35 |
IT Equipment |
|
Wireless |
14.99 |
50 |
IT Equipment |
|
USB |
9.99 |
60 |
IT Equipment |
|
Office |
149.99 |
10 |
Furniture |
|
Ergonomic |
189.99 |
12 |
Furniture |
|
Lockable |
119.99 |
8 |
Furniture |
|
Meeting |
249.99 |
5 |
Furniture |
|
Bookshelf |
89.99 |
15 |
Furniture |
|
Multi-Surface |
3.99 |
80 |
Cleaning Supplies |
|
Hand |
2.99 |
100 |
Cleaning Supplies |
|
Bin |
5.49 |
70 |
Cleaning Supplies |
|
Paper |
6.99 |
60 |
Cleaning Supplies |
|
Disinfectant Wipes (Pack 100) |
4.99 |
90 |
Cleaning Supplies |
C). Display all the tables with the meaningful inserted data.
Task 2: (1200 words equivalent)
(40 marks)
(LOs: 3)
Task 3: Test the implementation of the Discover College Purchasing System
Using the Discover Purchasing System database from Task 2, provide solutions to the following four problem cases in the form of SQL code and a brief explanation of your reasoning. Include screenshots of SQL queries execution and its results along with your schema, evidencing this in each of the cases mentioned below. Ensure your schema or username is included in every screenshot to verify it is your code. Case 1
List the products in the database with price in ascending order.
Case 2
To demonstrate the efficiency of searching to the principal, create searches based on the criteria mentioned below.
a. Find all the purchase orders made by each department. You must include the department name, the purchase order id, the date of the order and the status of the order.
b. Find products where the stock level is getting low in the cleaning supplies category. A stock level less than 90 is considered low.
Case 3
The product stock at Discover has changed. Write the queries to reflect these changes in inventory. Please show screenshots of database records before and after to show changes in the state of the database.
a. The USB Flash Drive 64GB has been renamed USB Flash Drive 128GB and the price has been changed to 20 pounds. Update the database accordingly.
b. The Meeting Room Table will no longer be purchased. Remove the row in the database to reflect this.
Case 4
Write queries to perform the following operations.
a. The purchasing manager would like to know the stock levels of each product. Output the supplier name the product name and the stock quantity of the product.
b. List the amount of each product purchased in descending order. Output the product and the amount of each product purchased
c. Find the total amount spent by each department on orders. Output the department name and the total amount spent
Task 3: (900 words equivalent)
(30 marks)
(LOs: 1)
End of questions
Reminder:
Make sure to use your university-allocated database account to answer the questions of Task 2 and 3, otherwise, your submitted work will not receive any marks for the tasks.
Provide execution screenshots along with their output in the report evidencing each step of your work, as well as text-based copies of any SQL. Also, ensure your username is included in all the screenshots of the database execution evidence to verify it is your work.
Learning outcomes (LO)
By completing this assessment, you will have shown and be assessed on all three of the learning outcomes:
1. Demonstrate a knowledge and understanding of databases and data management systems.
2. Identify key principles of relational database design techniques.
3. Apply knowledge and understanding of database design and development to design and implement a relational database solution.
You will be graded based on how well you meet these learning outcomes. Your marker will use a marking matrix to grade your work, and you can access this by clicking on the submission portal.
Guidelines and policies
You can find links to more useful information about the assignment and university policies below.
|
Word/time limit |
QA 87 – Penalties for Exceeding Assessment Size Limits Oct 2024.pdf |
|
Referencing guidelines Please |
Click here for Harvard referencing guidelines
|
|
Academic integrity and misconduct policy |
Click here to view Arden University’s policy on academic integrity and misconduct |
|
Statement on use of |
Click here to view Arden University’s statement on the use of artificial intelligence on assessment |
|
Support information |
Click here to view guidance on how to apply |
for short-term extensions
Click here to view guidance on how to apply for extenuating circumstances
Please click here for link to academic skills team support
