首页
网站开发
桌面应用
管理软件
微信开发
App开发
嵌入式软件
工具软件
数据采集与分析
其他
首页
>
> 详细
代写QHE5701、SQL程序设计代做
项目预算:
开发周期:
发布时间:
要求地区:
QHE5701 – Database Systems 2024
Coursework Assignment
Design and Implementation of a Relational Database Application Using MYSQL
Title:
Submission Deadline: Sunday 15th December 2024 (23:59 China Standard Time).
Any late submission will have penalty as per QMUL regulations.
Nursing House Record System
Introduction
You have been contracted to design and develop a database system for a small sized nursing
facility for senior citizens.
This family-owned nursing facility where all the records were managed on pen and paper, yet
it has grown to the point where this method has become unfeasible and are looking to
professionalise the management of the operation.
From client’s perspective, any senior resident record should store information about the
resident’s identity, differentiated by a unique ID (for this, Chinese National ID number is used).
The record should also include the resident’s name, their permanent address, contact phone
number, date of birth, gender, any known allergies, and a special field to store health and carerelated
notes about the resident.
The system supports four primary roles for managing resident data: caregivers, nurses,
administrative staff and guardians.
1. Caregivers can add, modify, or delete their personal care and assistance records for residents.
They can also view the complete care records for the residents assigned to them.
2. Nurses have permissions to add health-related treatment records for a resident. They can also
browse the resident’s visit history in a restricted manner, meaning they cannot view detailed
care or medical records created by caregivers unless necessary for their responsibilities.
3. Administrative staff can view the resident’s basic identity information, such as their name,
contact details, and room assignment. They can also monitor resident activity logs, schedule
visits, and ensure caregivers and nurses are appropriately assigned to residents based on
availability and needs.
4. Guardian is the contact person of the resident in case of emergency. Guardian is responsible
for paying the charges for the resident’s stay at the nursing house. Guardian’s data needs to be
stored in the database including their name, address (house, street, city, postal code), contact
number, wechat contact along with their relation with the resident is to be recorded. In some
cases, there can be two guardians for same resident, but this is optional. Not all residents have
two guardians.
Additionally, a database administrator role is introduced for managing user permissions
dynamically. This includes defining roles, assigning users to roles, and handling role
modifications or revocations (e.g., when caregivers, nurses, or staff members are hired or leave
the facility).
This design ensures that resident information is securely stored and accessed according to
specific role-based permissions while maintaining a clear separation of duties among
caregivers, nurses, staff, and administrative staff.
General
You are required to design and implement this database system through data requirements
analysis, conceptual design using concepts of the Entity-Relationship (ER) model, logical
design using the concepts of the relational model, and implementation and testing using
MySQL.
The specification above details the minimal requirements, you can design above or improve
the proposed design. For each change you are required to document the rationale for the
improvement. Include any assumption that you made in the data modelling.
Task 1 – Requirement Analysis:
(a) Background:
A short report about your understanding of the provided user requirement for which you are
designing the database (DB). This includes knowledge about the scenario which you used for
designing the DB.
(b) Assumptions:
Add 3 to 6 assumptions that inform your DB design. These assumptions should be relevant to
the DB. Mention any assumptions you made for the Data Model. Different assumptions lead
to different data model.
Example:
A student registers for exactly one course is relevant. "a student has a name" are not relevant.
Other relevant assumptions:
"A booking involves a customer, a receptionist, and several rooms booked; should be
modelled as a relationship; but we model booking as an entity and use relationships such as
customer_makes_booking."
Task 2 – Conceptual Design/ER Model:
Create a conceptual schema for the above database system using the concepts of the EntityRelationship
(ER) model. Explain the relationships modelled in your relational schema.
Show the following design steps:
(a) A textual description of the entity-relationship model including keys
(b) Description of the entities and relationships
(c) Explanations for cardinality and participation constraints
Example:
Complete the ER diagram by using the notation shown in the example of the Student, Course
entities and Enrol relationship given below. (NOTE: This is only for guidance on where to
start. It is not a required Format).
Entities:
Student{StudentNo, fName, lName, address, gender, NIN, compID, DOB, ProgrammeTitle}
Primary key: StudentNo
Foreign key: ProgrammeTitle references Programme(pTitle)
Alternate key: NIN
Course{CourseNo, CourseTitle, Credits, FacultyID, DeptID}
Primary key: CourseNo
Foreign key: DeptID references Department(DeptID)
Foreign key: FacultyID references Faculty(FacultyID)
Alternate key: CourseTitle
Relationship:
Enrol (many-to-many relation between Student and Course)
Enrol {StudentID, CourseID}
Primary key (combination of two FK): StudentID + CourseID
Task 3 – ER Diagram:
(a) Partial ER Diagram:
Draw partial ER diagrams showing each entity and all its attributes. You can use any tool (not
restricted to use MS-Visio, Draw.io, Lucid Chart or MySQL).
Example: Entity – Person
(b) Complete ER Diagram:
Graphical representation of your ER model. Your ER diagram must make use of the building
blocks of ER diagrams including primary key (simple or composite), alternate key,
composite attribute, multi-valued attribute, attribute of a relationship, recursive
relationship, 1:1 relationship, 1:m relationship and m:n relationship, among others. All
constraints should be considered including cardinality and participation constraints.
Example:
ER Diagram of a Hotel Reservation System.
Person
personID
email
firstname lastname
phone
(c) Textual Description of ER:
Provide a textual description of your ER Model. Your graphical diagram must correspond to
the textual description.
Example:
Enrol{StudentID, CourseID, EnrolmentDate}
– PK (combination of two FK): StudentID + CourseID
– when mapping the ERM to tables, we noticed that attribute EnrolmentDate makes sense
Task 4 – Mapping ER to Logical Model:
Provide mapping of the conceptual model to the logical model. Explain the main mapping
steps. Show the logical model (tables).
Example 1:
Logical model of a small sized company that sells Droids.
Source: BlueCorpSolution
You can also create the logical model in MySQL.
Example 2:
Logical model of a retail company.
Source: Binus
You can also use the EER diagram created in MySQL instead of the designing a logical model
separately. The EER diagram provide detailed overview of the underlying data structures,
presenting a detailed representation of the data model including tables, columns, data types,
indexes, and storage details.
Example:
EER diagram of a University Parking.
Task 5 – Normalisation:
Explain if and why your schema is in 3NF. Even if your schema is already in 3NF, construct
an example for 2NF and 3NF to demonstrate where in your design a normalisation step
occurred (excluding the normalisation of address, post-code -> city).
Task 6 – Implement the Database
Based on your schema, we ask you to create a minimum of 5 tables using SQL CREATE
commands. Create the tables and relationships between tables for database application using
MySQL. All the elements shown above in blue (Task 3) in your relational schema must be
properly implemented.
Remember to enforce the Referential Integrity including update rules on each of the
relationships between tables. We expect to see an instance of Generalisation.
Task 7 – Populate the Tables with Data
Populate the tables with meaningful data. You should populate your tables with at least 10 rows
of data per table using SQL INSERT statements. Enter sufficient data that reflect the
relationships' structural constraints (i.e., participation constraints and cardinality ratio
specified with ‘min..max’) and test the queries in Task 8.
Task 8 – Query the Database
Write at least 3 basic, 3 medium and 3 advanced queries using SQL and run them in your
database and add screenshots of your queries result in the final report.
(a) Basic queries:
Write 3 basic but non-trivial SQL queries using WHERE, JOIN or OPERATORS (AND, OR,
BETWEEN etc). SELECT * FROM table will not be accepted.
Example:
Customers who booked a room whose price is greater than 200.
(b) Medium queries:
Write 3 (only 2 if you're working in a group of 2 members) medium queries using GROUP BY,
HAVING, and complex JOINS (self-joins, outer joins).
Example:
Customers who booked a comfort room and a luxury room, all rooms with the customers who
booked them including the rooms no customer has booked.
(c) Advanced queries:
Write 3 (2 if you're working in a group of 2 members) advanced queries (NESTED Queries,
SET-based conditions or AGGREGATIONS).
Example:
Show customers who booked more rooms than the average customer (i.e. the average number
of rooms), all customers who booked mostly luxury rooms (e.g. more than 90% of the bookings
were for luxury rooms).
Task 9 - Database Application
Develop and implement an application that will allow the database users to access and retrieve
data from the database. The application should have a 'user friendly' graphical interface. The
application should allow the users to perform the following:
a) Run Use Cases for the System.
b) View data in Tables in datasheet view.
Task 10 – Considerations on Privacy and Security
The design presented above has no specific data privacy and security requirements. You are
requested to produce a one-page critique of the issues that can potentially become incidents.
You should include:
- An Identification of Sensitive data
- Data protection measures that you would add to the design.
- GDPR compliance. Reflect on data minimization, purpose limitation, storage
limitation, and data subject rights (e.g., right to access, rectify, or erase personal data).
Provide recommendations on how the system can adhere to these principles and ensure
the lawful processing of personal data.
Task 11 – Critical Evaluation
Submit a critical assessment of your work, as well as the value of this coursework in
understanding and using (or otherwise) the techniques and methods you learned to design and
implement a relational database. A brief statement of ‘individual contributions’ must be
included in here from each member of the group. This section should not be longer than A4
page.
Task 12 – Viva:
You are required to demonstrate your database application through a MS Teams meeting of 10
minutes. Each of the group will be sent an invite to MS Teams Meeting which will be scheduled from Monday (16th December 2024) to Tuesday (17th December 2024). These sessions will be
recorded as per QMUL regulations. Schedule will be shared in Week – 16.
Final Coursework Submission:
• Include a title page with your full name, module name, QMUL ID Number, BUPT ID
Number, Class Number, and qmul email.
• Use a clear and organized layout.
• Clearly label each task (e.g., Task 1 (a), Task 1 (b), Task 2 etc.)
• Your coursework report (pdf) should consist of all tasks (the SQL code and result as
screenshots). This report should explain the semantics of your relational schema.
• You must submit your SQL scripts in ONE .sql file. The SQL script MUST work
without ANY issues on MySQL. If the script does not run, you will get at most 40% of
the total marks.
• Create a folder and place all your database files (coursework report (.pdf), your SQL
scripts in a single file (.sql), database application files, and any additional file you
created for your ER Diagram within the folder.
• Compress this folder and rename it with your QMUL ID numbers of all group members:
221155XXX_ 221155XXX_ 221155XXX.zip
• Upload the compressed file on the Coursework Submission in Assessment Tab on
QMPlus module page.
Using ChatGPT or any generative AI tool is not recommended for this coursework.
However, if you choose to use such any GenAI tool, please ensure you properly cite it in
your report to avoid penalties related to plagiarism or academic misconduct.
Marking Scheme:
Requirement Analysis [5 Marks]
Conceptual Design/ER Model [5 Marks]
ER Diagram [10 Marks]
Mapping ER to Logical Model [10 Marks]
Normalization [5 Marks]
Implement the Database [10 Marks]
Populate tables with data [5 Marks]
Queries Basic
Critical Evaluation
Good Luck!
软件开发、广告设计客服
QQ:99515681
邮箱:99515681@qq.com
工作时间:8:00-23:00
微信:codinghelp
热点项目
更多
代做 program、代写 c++设计程...
2024-12-23
comp2012j 代写、代做 java 设...
2024-12-23
代做 data 编程、代写 python/...
2024-12-23
代做en.553.413-613 applied s...
2024-12-23
代做steady-state analvsis代做...
2024-12-23
代写photo essay of a deciduo...
2024-12-23
代写gpa analyzer调试c/c++语言
2024-12-23
代做comp 330 (fall 2024): as...
2024-12-23
代写pstat 160a fall 2024 - a...
2024-12-23
代做pstat 160a: stochastic p...
2024-12-23
代做7ssgn110 environmental d...
2024-12-23
代做compsci 4039 programming...
2024-12-23
代做lab exercise 8: dictiona...
2024-12-23
热点标签
mktg2509
csci 2600
38170
lng302
csse3010
phas3226
77938
arch1162
engn4536/engn6536
acx5903
comp151101
phl245
cse12
comp9312
stat3016/6016
phas0038
comp2140
6qqmb312
xjco3011
rest0005
ematm0051
5qqmn219
lubs5062m
eee8155
cege0100
eap033
artd1109
mat246
etc3430
ecmm462
mis102
inft6800
ddes9903
comp6521
comp9517
comp3331/9331
comp4337
comp6008
comp9414
bu.231.790.81
man00150m
csb352h
math1041
eengm4100
isys1002
08
6057cem
mktg3504
mthm036
mtrx1701
mth3241
eeee3086
cmp-7038b
cmp-7000a
ints4010
econ2151
infs5710
fins5516
fin3309
fins5510
gsoe9340
math2007
math2036
soee5010
mark3088
infs3605
elec9714
comp2271
ma214
comp2211
infs3604
600426
sit254
acct3091
bbt405
msin0116
com107/com113
mark5826
sit120
comp9021
eco2101
eeen40700
cs253
ece3114
ecmm447
chns3000
math377
itd102
comp9444
comp(2041|9044)
econ0060
econ7230
mgt001371
ecs-323
cs6250
mgdi60012
mdia2012
comm221001
comm5000
ma1008
engl642
econ241
com333
math367
mis201
nbs-7041x
meek16104
econ2003
comm1190
mbas902
comp-1027
dpst1091
comp7315
eppd1033
m06
ee3025
msci231
bb113/bbs1063
fc709
comp3425
comp9417
econ42915
cb9101
math1102e
chme0017
fc307
mkt60104
5522usst
litr1-uc6201.200
ee1102
cosc2803
math39512
omp9727
int2067/int5051
bsb151
mgt253
fc021
babs2202
mis2002s
phya21
18-213
cege0012
mdia1002
math38032
mech5125
07
cisc102
mgx3110
cs240
11175
fin3020s
eco3420
ictten622
comp9727
cpt111
de114102d
mgm320h5s
bafi1019
math21112
efim20036
mn-3503
fins5568
110.807
bcpm000028
info6030
bma0092
bcpm0054
math20212
ce335
cs365
cenv6141
ftec5580
math2010
ec3450
comm1170
ecmt1010
csci-ua.0480-003
econ12-200
ib3960
ectb60h3f
cs247—assignment
tk3163
ics3u
ib3j80
comp20008
comp9334
eppd1063
acct2343
cct109
isys1055/3412
math350-real
math2014
eec180
stat141b
econ2101
msinm014/msing014/msing014b
fit2004
comp643
bu1002
cm2030
联系我们
- QQ: 9951568
© 2021
www.rj363.com
软件定制开发网!