TAYLOR’S UWE DUAL AWARDS
PROGRAMMES JANUARY 2024 SEMESTER
DATABASE SYSTEMS
(ITS62904) Assignment – Group (30%)
STUDENT DECLARATION:
| No Student Name | Student ID | Date | Signature | Score | |
| 1 | |||||
| 2 | |||||
| 3 | |||||
| 4 | |||||
| 5 | |||||
Important Notes:
Submission:
It should be made electronically to the assignment submission section through your Classroom account. If there appears to be any problem at all with your submission, it is your responsibility to inform your lecturer immediately, via email,
Documentation: 2 separate files (Full assignment report & SQL File)
File Name: StudentName_StudentNo
Note: Late submission will be capped at 60 %
Academic Impropriety:
Submitting the course work means you have agreed that your work is original and comply with the rules and regulations of Academic Impropriety.
2
Note: Copying, cheating, attempts to cheat, plagiarism, collusion and any other attempts to gain an unfair advantage in assessment, result in awarding 0 marks to all parties concerned.
Case Study: Event Management Customizer (EMC)
SAT is a startup company based in Naxal; Kathmandu established in January 2022. SAT is planning to create an Inventory Management System and sell it to different clients. Inventory Management System helps track Inventories and various needed information regarding it.
Based on the software, clients with huge inventories of any kind of product can use the system. For operating the software clients are divided into multiple roles including unique client number, user role, admin role, and super admin role. Information like name, email, phone number, password, and whether it is enabled or not is stored for each user. The roles of each user are also maintained.
Users can add or edit products as needed. Various information regarding the product like name of the product, description, SKU, price, active status of the product, and category.
Each product has its own unique SKU and its product id. Normal users, super admin, and admin all can create products. There is an option to remove the active status which will remove or hide the product from the system software. Products will also have information about if they can be returned or not, are discontinued or not. Products that have returnable status can be returned by the user and the products which have discontinued status are discontinued and is not displayed in the product dashboard on the application.
Customers can buy the products. Each customer will have a unique id, full name, address (street address, state, city), pan number, phone number, email, and which user created the customer. Each product purchased by the customer can be viewed on sales which will show the customer’s details, a product sold, quantity, date, discount applied, the tax applied, subtotal, unique sales id, and unique sales number.
Store owners can also update their inventory by purchasing new products which can be viewed on purchases. Each purchase has a unique purchase id, which vendor the purchase was made from, the quantity of the product, discount if applied, tax if used, a unique purchase number, and which user created the purchase. Users can buy multiple products at once and all the details of all the products including all the product details, their individual price, discount, the tax applied and total price, total discount, and tax applied to all the products are displayed on the customers. Also, customers can buy many products at once and all the product details individual price, the tax also total product price, discount price, and tax applied is displayed.
3
TAKE NOTE:
The start-up team is also required to carry out findings (research) to improve the above requirement given. As a new team you need to be aware of the services that you plan to offer and how you are planning to bring these records into the database. The research phase is to bring the model to incorporate other relevant information that would make the database more useful for SAT (relevant to item 3 tabled below in business process change).
The above model requires some changes to cater for business process changes encountered from the year 2019. The detailed requirement has been tabulated to provide proper understanding of what is intended from the start-up team. Customer information, purchase and sales information, products, roles etc. have been detailed out clearly to provide a clear direction on what is intended to be part of the final database model required from the start-up team. Your task is to ensurethat given information is properly analyzed and considered for the database modeling phase. The Team lead has communicated the following information to you and expects SAT to deliver the changes to accommodate to the current system as soon as possible.
4
A. Design:(30 marks) 1. Entity-Relationship Diagram (ERD) – Map the entities with appropriate relationships.
2. Physical Model →Provide the necessary information for the Model (Relational Model – ensure data integrity).
5
B. Deployment:(20 marks) 3. Implementation: create database tables and populate the data (each table should have 20+ rows of valid records if it’s applicable). This may not be applicable for all tables. Product should sufficiently have at least 30 records.
6
C. SQL Report:(42 marks)
1. Provide a list of all the products currently active and list its categories.
2. Write SQL query to list down all the product details and number of sales of each product.
3. Provide a report that indicates the total number of purchases made by each user and all the details of each purchase including the total price, total discount provided, total tax and grand total.
4. Identify from the system the customers that have bought the highest number of products and highest amount in product.
5. Provide a report of all the products created by each type of users. 6. Write SQL query to list how many times each product has been purchased and also sold.
D. Unforeseen challenges: (8 marks)
1. Discuss about how the entire report was created including the roles and responsibilities of each team member, how the ER diagram was created, how database was designed and how it was converted to a physical database. Also explain what business requirements needed to be added to the system to make it easier to use.
Marks Tabulation process:
Students need to be aware that database design outcome affects the development process of the database. If the final report and design is not being properly considered then the implementation and roll- out carries the effect of the database design. In the case of poor database design, a simple reduction of markswith design, will be carried to development as well. Incorrect and inaccurate design leads to incorrect and inaccurate development as well, even though the implementation is completed.
6
ITS62904
Assignment–Group
Marking Rubrics (JANUARY 2024)
| Criteria | Score | ||||
| Excellent | Good | Average | Poor | ||
| >= 90% of the marks | < 90% to >= 75% of the marks | < 75% to >= 40% of the marks | < 40% of the marks | ||
| A. Database design (ERD and Physical Model) | Both ERD and accurately. All the relationships are considered and highlighted properly. All the keys are defined and linked correctly. Necessary information for the physical model is provided sufficiently. The similarity is less than 2%. | Both ERD and Physical models are Physical models are Physical models designed and drawn designed and drawn accurately. Most of the relationships are considered and highlighted properly. All the primary and foreign primary and foreign properly. Some the keys are defined and linked correctly. Necessary information for the physical model is provided acceptably. The similarity is less than 4%. | Both ERD are designed drawn with minor mistakes. Some of the relationships are considered and highlighted primary foreign keys are defined and linked correctly. Necessary information for the physical model is provided acceptably or the information is missing. The similarity is less than 4%. | and Both ERD and Physical models and having major mistakes or any of they are missing. A few of the relationships are considered and highlighted. Rarely and the primary and foreign keys are defined and linked correctly. Necessary information for the physical model is missing. The similarity is more than 5%. | |
The deployment is The deployment is The deployment is The deployment is done with no error. done with minor done with Major done with Major All the tables errors. Most of the errors OR only a errors AND only a
B. Database having a sufficient tables have a few of the tables few of the tables deployment number or records. sufficient number having a sufficient having a sufficient The similarity is or records. The number or records. number or records.
less than 2%. similarity is less The similarity is The similarity is than 4%. less than 4%. more than 5%.
7
| C. SQL Report | All the SQL scripts are accurate with no error and the results are demonstrated. The similarity is less that 2%. | Most of the SQL scripts are accurate with no error and the results are demonstrated. The similarity is less than 4%. | Some of the SQL script are accurate with no error and the results are demonstrated with minor errors. The similarity is less than 4%. | Only a few of the SQL script are accurate with no error and the results are demonstrated with major errors. The similarity is more than 5%. |
| D. Handling with unforeseen challenges | Explanation is detailed and valid with relevant samples. The similarity is less than 2%. | Explanation is detailed and valid with samples but not very relevant. The similarity is less than 4%. | Explanation is available with samples but not very relevant. The similarity is less than 4%. | The explanation is not detailed and valid. It’s without relevant examples. The similarity is more than 5%. |
8