Subject Code & Title :- CO2102 Database Project
Assessment Type :- Assignment
Question 1 :-
One of the leading groups of high‐tech manufacturing companies’ CEO has approached you about a project they are currently working on. They design and manufacture games consoles laptops movie projectors and many other consumer gadgets. She has approached you to advise her about how to collect store and process data for the group’s new carbon accounting project code‐named DB Green. The CEO has put much thought into the data she wants to store in the carbon accounting database. She has also spoken to her IT management team to get their input. Between them, they have come up with a set of business rules to be used as the basis for the data model design.
CO2102 Database Project Assignment – UK
DB Green Project – Business Rules
1. A single database will be created that will track the carbon footprints of all the companies with in the group.
2. All tables in the database must have primary keys.
3. Each company has a name and consists of one or more departments.
4. Each company has one employee that is the CEO of the company. That employee may only be the CEO of one company, and not all employees are CEOs.
5. Each department must have exactly one employee who is the Green Champion – the person responsible for any initiatives related to the carbon footprint of the department. That employee may only be the Green Champion for a single department, and not all employees are Green Champions.
6. The following data must be recorded for each employee: name and surname.
7. Some departments have products that they are responsible for. Each product must be allocated to exactly one department and must have a description.
8. The main purpose of the database is to record carbon transactions. Each carbon transaction is accounted for by exactly one department. Optionally the single product that the carbon transaction relates to can also be recorded.
9. The following data must be recorded for each carbon transaction: date and value.
1.1. Draw an ER model according to the above business rules.
1.2. Now that the CEO knows that you are an expert with ERDs, he wants you to review an ERD that an intern created for another project. Below is the ERD and the business rules used to create it.
Which 10 changes would you propose should be made to the ERD to improve its quality so that it can be successfully implemented in a relational database?
Business Rules
1. All tables in the database must have surrogate primary keys.
2. A product consists of one or more parts. Not all parts captured in the system are part of a product yet and a part can be used in many products.
3. Each part is supplied by at least one supplier, but multiple suppliers could potentially supply the same part.
4. The following information must be stored for each product: description.
Question 2 :-
While the data model design process was still under way an enterprising accountant had already started putting together data in a spreadsheet. The CEO applauded his enthusiasm but she is worried about the integrity of the data even though the accountant says his data is in the first normal form already.
Here is a sample of the spreadsheet data in first normal form underlined columns names indicate the composite primary key):
Draw a dependency diagram to show the partial and transitive dependencies in this data.
Question 3
In this question you will create a full database schema for a pharmaceutical company. The following sections describe the full schema and components of the required database. A pharmaceutical company includes full information about its employees. This covers the employee number last name first name extension email address office code job title and the other employee in which he/she reports to (Hint: multiple employees can only report to one employee however one/several employees cannot report to multiple employees at the same time).
The company also stores information about their pharmaceutical retail customers, which covers the customer number customer full name last name and first name phone number address line 1 address line 2 city county postal code country sales amount representative employee number in which they deal with directly and the credit limit number of that customer.
Further more the pharmaceutical company database stores information about the drugs they produce and prescription orders. This covers the order number order date required date shipped date order status comments and the pharmaceutical customer number that relates to this order. In addition information about the individual drugs should cover the product code product name product line scale-weight vendor description quantity in stock the buying price and the MSRP manufacturer’s suggested retail price).
In relation to the employees information mentioned previously, the employees are assigned to offices with in the pharmaceutical company, and this information includes the office code City phone number address line 1 address line 2 county country postal code and territory. In addition with regards to the pharmaceutical retail customers the payments information are stored in correspondence to each customer. This includes the customer number cheque number payment date and the amount paid by each customer.
The mutual information between the drugs/products and the orders include the order number product code quantity ordered price and the order line number. Finally this database stores the information about pharmaceutical product lines which includes the product line text description website and the product image.
A product line can include multiple products, however, one product cannot belong to more than one product line. Furthermore an order can include many products and a product could also be related to multiple orders. In addition one retail customer can make multiple payments however one payment can only be related to one customer at a given time. Several employees with in the pharmaceutical company can report to one employee only and they cannot report to multiple employees at once. One office can have more than one employee yet one employee should not be assigned to more than one office.
3.1 Create an ER diagram using the class diagram method to represent the above description. Your diagram should illustrate all the attributes entities keys and relationships. For all entities make sure to accurately represent each relationship name, type and degree.
3.2 Write SQL DDL commands to create the previous database schema. Make sure to represent your exact ER diagram from the previous task, and achieve the relationships, data types, and the correct attributes for the corresponding entities. You must also make sure that the order of your SQL commands is correct to ensure data integrity.
CO2102 Database Project Assignment – UK
3.3 Write MySQL commands to insert 4 rows in each table you have previously created. You should create your own unique data (BE AWARE OF PLAGIARISM). In addition you should make sure that the entered data makes sense against each table. In particular make sure to comply with the correct data types constraint rules and the relationships in which you have set previously. You must also make sure that the order of your SQL commands is correct to ensure data integrity when executing the SQL script.
3.4 Create a view called Customer_Order_Restricted_Info which shows the customer id customer full name first name and last name as one column separated by space customer full address which shows address line 1 address line 2 postal code and country all as one column and separated by spaces and separate information about the order status and quantity ordered which are relevant to each customer with a credit limit of more than 1000, and an order shipped date that took place before 2010.
CO2102 Database Project Assignment – UK
Marking Scheme: How is this Project Marked?
Each section of the work will be assessed in accordance to the marks assigned to it in the marking scheme.
This is outlined below in accordance with each section/task.
In order to ensure consistency and fairness, individual feedback will be provided on the system against each given mark.
Your MySQL solution should run successfully on MySQL Workbench. Failed to do so, 50% of the overall mark will be deducted, and then your solution will be marked accordingly.
All ERD must be drawn using draw.io or any other graphical tool. ERD drawn with pen and paper will result in 20% deduction from the overall mark.
This assignment weighs 50%, and is calculated out of 100.
The mark distribution is assigned as follows:
o Question 1.1 – 20%
o Question 1.2 – 10%
o Question 2 – 15%
o Question 3.1 – 15%
o Question 3.2 – 20%
o Question 3.3 – 5%
o Question 3.4 – 15%
A full sample solution of the assignment will be provided after the submission date.
ORDER This CO2102 Database Project Assignment NOW And Get Instant Discount
Read More :
BCLA6012 Computer Law And Artificial Intelligence Assignment – UK
