TAYLOR’S UWE DUAL AWARDS PROGRAMMES JANUARY 2024 SEMESTER DATABASE SYSTEMS (ITS62904) Assignment – Group (30%) 

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 ensure that  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 

Tasks:  

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 marks with 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  

DATABASE SYSTEMS

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