首页
网站开发
桌面应用
管理软件
微信开发
App开发
嵌入式软件
工具软件
数据采集与分析
其他
首页
>
> 详细
COMP2400代写、R程序语言代做
项目预算:
开发周期:
发布时间:
要求地区:
COMP2400/6240 - Relational Databases
Assignment on Database Theory
Due date: 23:55, 11 October 2023
Instructions:
• This assignment must be done individually (no group work).
• This assignment will count for 15% of the final grade. Marks are assigned for the process of finding a solution,
not only for the result. Hence, include all essential ideas and steps that are necessary to derive a solution.
• You must submit a single PDF file named as “u1234567.pdf” (replace u1234567 with your UID). Make sure you
only upload a PDF file, not a Word or text file.
• You should try your best to type the solutions. The scanned images of handwritten texts and equations can
be unreadable for marking. As for the EER diagram, you are highly recommended to export a JPEG file from
TerraER and include it in the PDF file.
• Late submission is not granted under any circumstance. You will be marked on whatever you have submitted at
the time of the deadline. Please take careful note of deadlines and adhere to them. Of course, if you find yourself
in a situation beyond your control that you believe significantly affects an assessment, you should submit an
Assessment Extension Request through Wattle along with the supporting documents.
• Plagiarism will attract academic penalties in accordance with the ANU guidelines. A student in this course is
expected to be able to explain and defend any submitted assessment item. The course convener can conduct or
initiate an additional interview about any submitted assessment item for any student. If there is a significant
discrepancy between the two forms of assessment, it will be automatically treated as a case of suspected academic
misconduct.
Question 1 4 Marks
A real-estate agency named HomeComfort which has recently been founded in Canberra wants to setup a database to
accomodate the following requirements.
HomeComfort has opened 5 offices in different districts of Canberra including Canberra Central, Belconnen, Gungahlin,
Tuggeranong and Weston Creek, respectively. It is planning to open two more offices in Molonglo Valley and Woden
Valley in the future. An office has a unique address but may have multiple telephone numbers. Each property is
associated with exactly one office of HomeComfort, and is managed by a property manager from that office. A
property can be identified by its address and has the information about the area and the number of rooms. Each
employee working at HomeComfort has a tax file number (TFN), a name and an address. HomeComfort’s employees
are categorised into one of the following four categories: 1) office manager, 2) property manager, 3) property inspector,
and 4) customer service representative. Every employee works at only one office of HomeComfort. A customer at
HomeComfort is identified with a unique customer ID, a name and a contact number. A customer can buy or rent
one or more properties and details about that property such as the rental period or pay-off period and price should
be recorded. If a customer is not satisfied with the possessed property, this customer may submit a complaint to
a customer service representative and thus a distinct reference number and detailed description of the complaint
should be recorded. The relevant office manager will handle all the complaints with respect to this office and has an
urgent contact number in case of emergency. Property inspectors inspect the properties and record the condition and
inspection date. A property may be inspected by any of the property inspectors from the associated office, but must
be inspected at least four times per year. Each property inspector has a diploma in building inspection and might have
another property inspector as the supervisor. The supervisor must have a higher skill level compared to the property
inspectors supervised by this supervisor.
Your task is to design an Enhanced Entity Relationship (EER) diagram for the above database, which should include
entities, relationships, attributes and constraints wherever appropriate. Note that you can make more assumptions if
necessary and provide them with your solutions.
You also need to identify the requirements that cannot be captured in an EER-diagram.
Question 2 5 Marks
1
Consider the relation schema R={A, B, C, D, E} and the following set Σ of FDs:
• AE → BC
• B → A
• AB → DE
• C → A
2.1 What are the candidate keys of R? Justify your answer (i.e., include the main steps used for finding the candidate
keys). (2 Mark)
2.2 Find a minimal cover of Σ. Justify your answer (i.e., include the main steps used for finding a minimal cover).
(2 Mark)
2.3 Demonstrate whether R, given Σ, satisfies 3NF, if not, then identify a 3NF decomposition for R. You need to
include the main steps used for identifying the 3NF decomposition. (1 Marks)
Question 3 2 Marks
Consider the relation schema Meeting={Client, Date, Employee, Room, Branch} and the following set Σ of FDs:
• Branch, Date, Employee → Room
• Branch, Date, Room → Employee
• Branch, Client, Date → Room
• Client, Date → Employee
Is the above relation schema Meeting in BCNF? If not, identify a BCNF decomposition for Meeting. You need to
include the main steps used for identifying the BCNF decomposition. Check if this BCNF decomposition is dependency
preserving. (2 Mark)
Question 4 4 Marks
The following table contains the relational algebra operators covered in our course. You may only use these operators
to answer the following questions.
σφR Selection by condition φ
πA1,...,An R Projection onto the set of attributes {A1 . . . , An}
ρR
′
(A1,...,An)R Renaming the relation name to R
′
and attribute names to A1, . . . , An
ρR
′R Renaming the relation name to R
′
ρ(A1,...,An)R Renaming the attribute names to A1, . . . , An
R1 ∪ R2 Union of two relations R1 and R2
R1 ∩ R2 Intersection of two relations R1 and R2
R1 − R2 Difference of two relations R1 and R2
R1 × R2 Cartesian product of two relations R1 and R2
R1 ▷◁φ R2 Join of two relations R1 and R2 with the join condition φ
R1 ▷◁ R2 Natural join of two relations R1 and R2
φ1
V
φ2 condition φ1 AND condition φ2
φ1
W
φ2 condition φ1 OR condition φ2
2
Consider the following relational database schema S of a booking system:
CUSTOMER = {CustomerID, FirstName, LastName, Phone}
PK: {CustomerID}
STAFF = {StaffID, FirstName, LastName}
PK: {StaffID}
HOTEL = {HotelName, RoomNo, Phone}
PK: {HotelName, RoomNo}
BOOKING = {CID, SID, HNm, RNo, Date, Cost}
PK: {CID, SID, HNm, RNo, Date}
FK: [CID] ⊆ CUSTOMER[CustomerID],
[SID] ⊆ STAFF[StaffID],
[HNm, RNo] ⊆ HOTEL[HotelName, RoomNo]
4.1 Answer the following questions using relational algebra queries only using the operators in the above table. You
are encouraged to use relational algebra expressions to represent intermediate results if needed.
[a] Find all those staff members who have never created any booking for a hotel named “PearlOrient” (i.e., HNm =
‘PearlOrient’). List their StaffIDs. (1 Mark)
[b] Find all those customers who have booked exactly one hotel room on 27-09-2023. List their CustomerIDs, First
Name and Last Name. (1 Mark)
4.2 Optimise the following relational algebra query (your marks will depend on how well you present the key ideas of
query optimization in your answer). In addition to this, draw the query trees that correspond to the query before and
after your optimisation.
πCustomerID,F irstName,LastName,Date,HNm(σ(CID=CustomerID)
V
(HNm=HotelName)
V
(RNo=RoomNo)
V
(Cost>150)
(Customer × Booking × Hotel))
(2 Mark)
+++++
3
软件开发、广告设计客服
QQ:99515681
邮箱:99515681@qq.com
工作时间:8:00-23:00
微信:codinghelp
热点项目
更多
cis432代做、代写python/java程...
2024-05-04
eeen3007j代写、c++程序设计代...
2024-05-04
代写data程序、代做c/c++, jav...
2024-05-04
comp2006代做、代写c++程序语言
2024-05-04
comp26020代做、java/c++设计编...
2024-05-04
csci251 advanced programming...
2024-05-03
cs 6290: high-performance co...
2024-05-03
assignment 2: executing and ...
2024-05-03
ecse427/comp310 programmin...
2024-05-03
cs 452 (fall 22): operating...
2024-05-03
comp9414 23t2 assignment 2 ...
2024-05-03
dpst1091 23t1 assignment 2 ...
2024-05-03
program代做、代写python设计编...
2024-05-03
热点标签
finm8007
comp2006
comp26020
comp1721
eeen3007j
cis432
csci251
comp5125m
com398sust
32022
mth6158
comp328
finn41615
2024
mec302
mgmt3004
mgt7158
com160
as.640.440
econ3016
finm7405
econ7021
fin600
infs4205/7205
mktg2510-
f27sb
csse2310/csse7231
rv32i
eecs 113
comp1117b
cs 412
comp 315
econ7300
comp2017
ecs 116
fit5046
com6511
comp30024
acs341
econ1020
isys3014
acc408
comp1047
csc 256
cs 6347
finm7008
comp34212
csmde21
estr2520
comp285/comp220
mds5130/iba6205
finc6010
is3s665
busi2194
125.785
iom209
msin0041
econ339
cmt218
mast10007
comp5349
ecx2953/ecx5953
bios706
comp3310
mth6150
comp30027
comp20005
eec286
busi2211
bff2401
fnce90046
visu2001
mang6554
finc6001
125785
data423-24s1
engi 1331
fint2100
(520|600).666
can202
cs 61b
mast20029
info20003
stat512
econ3208
cmpsc311
engg1340
ecmt1010
fit5216
basc0003
ee3121
acct2002
comp5313
busi2131
ise529
elec372/472
csit940/csit440
cenv6141
comp3027/comp3927
ftec5580
comp1433
msci223
mark203
en3098
eden1000
ece6483
econ4410
mats16302
cs 6476
com6521
comp222
comp3211
comp10002
csc1002
chc6186
cs 161
comp27112
comp282
swen20003
comm1190
elec9764
acfi3308
acct7101
fin6035
comp2048
geog0163
comp2013
coen 146
dts101tc
sehh2042
comp30023
comp4880/8880
cs 455
07
stat0045.
fil-30023
celen085
psyc40005
math40082
are271
comp9311
ee5311
imse2113
comp 2322
acct2102
fnd109
int102
is3s664
is6153
data4000
accfin5034
fit5212
cs536-s24
fit5225
ecos3006
mes202tc
finc5001
stat3061
csc171
cs1b
7ssmm712
bu.450.760
cs170
comp3411
swen90004
cpt206
comp5313/comp4313—large
bl5611
kxo206
comp532
elec207
kxo151
cs 2820
cpt108
math2319
dts204tc
qm222
comp2511
ccs599
infs1001
mat2355
eeee4123
25721
ifn647
pols0010
hpm 573
qbus6860
comp9417
csci 1100
stat0023
cse340
comp2003j
cs 2550
cs360
fin 3080
ierg 4080
cs6238
cit 594
finm7406
hw6
联系我们
- QQ: 9951568
© 2021
www.rj363.com
软件定制开发网!