CPT103编程辅导、辅导sql语言程序、sql编程设计调试 辅导留学生 Statistics统计、回归、迭代|解析Haskell程序
- 首页 >> Python编程 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.
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.