联系方式

  • QQ:99515681
  • 邮箱:99515681@qq.com
  • 工作时间:8:00-23:00
  • 微信:codinghelp

您当前位置:首页 >> Database作业Database作业

日期:2020-12-04 09:20

CPT103 COURSEWORK

3 – PART 1

Module leader: Jianjun CHEN

Release date: 20 Nov 2020

Deadline: 23:59, 4 Dec 2020

Background Information

DurianPC aims to become a top-notch manufacturer of desktop and

laptop computers in the Lukewarm Kingdom. The company was just

formed recently but has already received abundant funding from

investors all over the kingdom. The company has made a lot of decisions

on the structure and operations of the company, which will be shown in

the next section. In this coursework, your task is to:

1. First, design a database that works under the company’s

structure and operating model. The database must be in the third

normal form (3NF). Tables should not have m:m relationships.

2. Design necessary INSERT/UPDATE statements that can

modify the database based on the company’s requirements.

3. Design necessary SELECT queries that can fetch needed information for the company.

There is one extremely important thing you need to keep in mind: if you copy any code from others or let

someone else develop this database or any other part of this project, the manager will be disappointed and

immediately report this to me. By attempting this coursework, you agree to not plagiarise in any parts.

Plagiarism and collusion lead to a zero mark for this coursework.

The Structure of the Company

The owner of DurianPC has decided that the manufacturing will be carried out in a factory in the north region

of the kingdom while the administrative staff will work in the headquarters (HQ) located in the centre of the

kingdom. Each work place is associated with a unique postcode. Apart from the factory and the HQ, the

owner of the DurianPC also wants to provide customers with places to experience his products. As a result,

many retail stores will be constructed at different places across the Lukewarm region. All staffs in these

retail stores are considered employees of the company, belonging to the sales department. Each retail store

is associated with one postcode.

Staff is organised by departments in the company. Each department has a department name and some

descriptions. Each staff is assigned to one or more roles in his department. Two or more persons may share

the same role in the department so that the workload can be split. Like departments, roles are also associated

with some descriptions. Two departments may have roles with the same name, so be careful when designing

your database. Offices are usually allocated to staff by the department. However, staff members from

different departments can occasionally share offices. Each staff has a unique phone number, a computer

account that is a string no longer than 15 characters and an email address. Sometimes, an email address is

given to a role shared by many staff members. For example, the human resource (HR) department has a

shared email account HR@durian.pc and the finance office has finance@durian.pc. The company should

also be able to identify what department an email address belongs to and the person(s) who use it by

searching the database. In addition to the above requirements, the IT department will ask a user to change

his email password 1 year after the last time he changes his password.

Individual staff performance is evaluated every year to decide their salary. There’s a salary baseline for each

individual decided when the working contract was signed. High performers can receive up to 20% salary

bonus. Staffs working in the south region of Lukewarm will receive a 10% salary bonus due to the high

temperature there. The “working” here means the employee’s office is located in that region. The region

information can be found by analysing the postcode. All postcodes in the south region start with “LS”.

All staff bonuses are added rather than multiplied. If one staff is being sick, he will receive 90% of his normal

payment (after counting all bonuses) without having to go to the company. For example, if an employee is a

high performer and works in the south region, he will have (100% + 20% + 10% = 130%) salary based on

his salary baseline. But if he gets sick and has to stay at home for a month, he will receive (130% * 90% =

117%) pay.

The financial department will pay salaries at the end of each month based on the performance records in the

database. Payment records must be stored in the database so that they can be retrieved in the future. The

amount of payment bonus or deduction, and the reason (just some text comments) should also be indicated

clearly along with the payment records.

The computers manufactured by DurianPC includes desktops and laptops. Each computer has a unique serial

number once manufactured. There are also different models for computers. Model numbers for desktops

start with “DT”, followed by 4 digits (e.g. DT4271). Model numbers for laptops start with “LC”, followed

by 4 digits, and then end with letter ‘P’ or ‘L’ (E.g. LC2333P). The letter ‘P’ means powerhouse and the

letter ‘L’ means feather-light. Before a computer is packaged and shipped, it will be checked thoroughly by

an employee for defects. A computer is made up of several parts (such as CPU, motherboard, graphics card,

screen and power supply). Each part is associated with a part type, a manufacturer, a model and a unique

serial number. Below is an example of a CPU installed on a computer:

1. Part type: CPU

2. CPU model: i5-4430

3. Manufacturer: Intel

4. Unique serial number: L070Q228

Each month, DurianPC will make a phone call to the part manufacturers to report common defects of their

parts. The company will also send the defected parts to the manufacturer if requested. You can assume that

phone numbers contain only digits. Your database should keep a record of all computers manufactured so

far, their manufacture time, the staff who did the quality assurance, each computer’s parts information as

well as whether this computer has been sold and from which retail store.

In this coursework, you can make some assumptions to the data if they are not specified in the requirements.

For example, you can assume address occupies up to 200 characters, or people’s name can be up to 30

characters long.

Task: Database Design (60% of CW3)

Based on the description in the previous section, design your tables for DurianPC. The table design should

be in the third normal form (3NF) and should not have m:m relationships in the ER diagram. You

need to provide both the ER diagram and the SQL create table statements. ER diagrams can be generated

automatically from phpMyAdmin or other tools. You don’t have to manually draw everything.

Write a report for your design and submit it as “cpt103_[your id].pdf” (for example, cpt103_1988227.pdf).

Do not submit “doc/docx” files, otherwise the coursework will just be marked based on how it looks on my

computer, which might look differently and you will not be given another chance to submit it. You should

keep your sentences precise and clear (and must be in English, any other languages will simply be ignored),

there is no minimum or maximum word requirement for this report.

The report should first present your ER diagram. You then need to write a discussion on your design, what

considerations were made when you were designing the database. Remember to list the functional

dependencies inside this table. Then state the reasons why a particular foreign key is needed and why certain

column(s) is chosen as the primary/unique key.

Submit your report and your SQL script, containing all CREATE TABLE statements and constraint

definitions, as “cpt103_[your id].sql” or “cpt103_[your id].txt” to the LearningMall. The submission

deadline is 23:59, 4 Dec 2020 and the cut-off date is 23:59, 8 Dec 2020. Late submissions will cost you 5

marks per day.


版权所有:编程辅导网 2018 All Rights Reserved 联系方式:QQ:99515681 电子信箱:99515681@qq.com
免责声明:本站部分内容从网络整理而来,只供参考!如有版权问题可联系本站删除。