M32275 Database Development and Implementation Coursework

This portfolio (coursework) has two parts:

-    The first part involves theoretical modelling. Given a scenario, you’ll need to create a partial or full entity-relationship diagram (ERD) using Lucidchart. At the same time, you should document the assumptions/business rules you have made about the relationships depicted.
-    The second part focuses on SQL programming. You will be provided with three data files and will need to use DB Browser to execute your SQL code. Be sure to capture relevant screenshots and include them in the submitted document.

Part 1: Entity-relationship modelling (theoretical practice)

Question 1:

In a software development company, a diverse team of developers, each proficient in one or more programming languages, collaboratively works on various projects. Each project involves multiple developers and requires a variety of programming languages. Every project is linked to one or more clients and is managed by a project manager who ensures alignment with client expectations. In addition to this, each client is assigned a dedicated client manager, acting as their primary point of contact. This ensures efficient communication between the company and the clients, thereby managing the complex system effectively.

Draw an ERD (without attributes), including entities and relationships with cardinality information. Write down the assumptions upon which your design is based in the submitted document.
(Use Lucidchart to draw ERD: https://lucid.app/documents/#/home?folder_id=recent)

Question 2:

Create an ERD (without attributes) for a bike shop showing the entities and the cardinality of each relationship. The shop sells both new and used bikes and also offers a bike repair service. Base your ERD based on the following business rules:
•    A salesperson may sell many bikes, but each bike is sold by only one salesperson.
•    A customer may buy many bikes, but each bike is purchased by only one customer.
•    Each bike sold by a salesperson has a separate invoice issued.
•    A customer receives an invoice for each bike he or she purchases.
•    A customer may visit the shop only for a bike repair, so buying a bike is not required to be classified as a customer.
•    When a customer brings in one or more bikes in for repair or service, one service ticket is issued for each bike.
•    The bike shop maintains a service history for every bike serviced or repaired with service records referenced by the bike’s serial number.
•    A bike that undergoes service or repair may or may not need parts replacement.

Write down your assumptions in each relationship upon which your design is based in the submitted document.
(Use Lucidchart to draw ERD: https://lucid.app/documents/#/home?folder_id=recent)

Question 3:

A job agency offers to find jobs for clients looking for employment. A database system is used to record the client’s personal and résumé details. Personal details include name, address, date of birth, gender, landline phone number, and mobile phone number. The résumé details include a history of employment and a set of qualifications. The employment history will include current position (if any) and a record of previous job positions, employer name, and final salary. The qualifications refer to an award (e.g., MSc), an awarding institution (e.g., University of Portsmouth), and graduation year. Each client is assigned to one job consultant within the agency who negotiates with the client and endeavors to locate suitable employment for them. A consultant will typically deal with a number of clients.

Draw a full ERD, including the relevant attributes, data type and size for the attributes, PKs and FKs to link the tables. Write down the assumptions upon which your design is based in the submitted document.
(Use Lucidchart to draw ERD: https://lucid.app/documents/#/home?folder_id=recent)

Part 2: SQL programming (coding practice)

Questions 4:

Suppose you are working as a data analyst for McDonald’s European division. Your manager has asked you several questions about the restaurant’s operations. You have been provided with three tables (data files) and are tasked with using SQL to answer the questions.
Here is some information about the three files (tables):

Sales: This dataset contains information about specific transactions, such as the product names, prices, quantities, costs, purchase types and payment methods.

Store: This dataset provides information about McDonald’s stores, including Store ID, country, region, general manager’s name, age, sex and tenure.

Theft: This dataset contains information about the stolen transactions.

Use DB Browser to create a database and then import the data files into it. Then, run SQL queries on the database created to answer the following questions.
I: What types of products are we currently selling?  (6 marks)
II: What is our average price for each product? (Please round the average prices to two decimal places) (8 marks)
III: How much does each manager have in theft amounts? (8 marks)
IV: Filter by the managers that have more than 5 in theft amounts and find their age, sex, tenure, and country_city location. (6 marks)
V: If your manager wants to know which country_city has the highest sales (in terms of quantity), what data cleaning steps should you take before running SQL SUM queries? Why is it important to clean the data first in this case? (6 marks)
VI: Assuming the data is already clean, use SQL to determine which country_city has the highest sales (in terms of quantity). (6 marks)
VII: Assuming the data is already clean, use SQL to list all the products by quantity sold from high to low. (8 marks)