Assessment 2 Part 1 week 7 final
.docx
keyboard_arrow_up
School
Queensland University of Technology *
*We aren’t endorsed by this school
Course
105
Subject
Computer Science
Date
May 9, 2024
Type
docx
Pages
11
Uploaded by PrivateKudu3534
ITD105 Database Management
Rob Baltrusch
Student ID: 666
Date: 08/09/2023, Trimester 2, 2023
Task 1(Transform familiar examples into elementary facts, and apply quality checks)
Employee elementary facts:
Surgeon:
A surgeon with Employee ID ‘748392’ has name ‘Stephen Larson’.
A surgeon with Employee ID ‘748392’ has date hired ‘13/04/2007’.
A surgeon with Employee ID ‘748392’ has Specialty ‘Orthopaedic’.
A surgeon with Employee ID ‘748392’ has Emergency Phone number ‘0428992442’.
Nurse:
A nurse with Employee ID ‘779432’ has name ‘Kate Joylan’.
A nurse with Employee ID ‘779432’ has date hired ‘13/04/2007’.
A nurse with Employee ID ‘779432’ has Certificate ‘Acute Care Nursing’.
Non-medical staff:
A non-medical staff with Employee ID ‘783273’ has name ‘Jason Reilly’.
A non-medical staff with Employee ID ‘783273’ has date hired ‘
17/05/2021
.
A non-medical staff with Employee ID ‘783273’ has employee ratio ‘
FT
’.
Surgery types:
A surgery type with surgery ID ‘849’ has name ‘Laparoscopy’ A surgery type with surgery ID ‘849’ has description ‘performed in the abdomen or pelvis using small incisions with the aid of a camera’.
Patients:
A patient with Patient ID ‘177347’ has name ‘Brian Middleton’.
A patient with Patient ID ‘177347’ has address ‘1 Sandpiper Pl, Wishart QLD 4122’.
A patient with Patient ID ‘177347’ has Insurance Company ‘Bupa’.
Operation:
A surgeon with Employee ID ‘763253’ has Patient ID ‘177347’ with Surgery ID ‘752’.
Assumptions:
Assuming that Operation is a ternary fact type.
Task 2(Draw the fact types, and apply a population check):
Surgeon:
748392
Stephen Larsson
763253
Thomas Ricks
752321
Julie Sommerville
793214
Lauren King
Nurse: 779432
Kate Joyland
798962
Sandra Moltar
702339
Ben Richardson
794323
Jake Anderson
(dd/mm/yyyy)
(.Name)
(.Num)
(dd/mm/yyyy)
(.Name)
748392
13/04/2007
763253
21/07/2011
752321
06/10/2015
793214
09/12/2022
748392
Orthopaedics
763253
Vascular Surgery
752321
Neurosurgery
793214
Orthopaedics
748392
0428992442
763253
0429458921
752321
0438586034
793214
0439594872
779432
25/11/2013
798962
15/08/2023
702339
17/05/1997
794323
08/04/2023
779432
Acute Care Nursing
798962
Orthopaedic Nursing
702339
Palliative Care
794323
Digital Health
Non-medical staff: 783273
Jason Reilly
793298
Ben Ricks
751243
Cassandra Stephenson
735253
Chador Sandeep
783273
FT
793298
FT
751243
PT
735253
CA
Surgery types:
(dd/mm/yyyy)
(.Name)
Description
783273
17/05/2021
793298
15/01/2023
751243
01/07/2015
735253
25/09/2006
849
performed in the
abdomen or pelvis
using small incisions
with the aid of a
camera
761
inspection of the
uterine cavity by
endoscopy with access
through the cervix
759
vermiform appendix is
removed
752
vascular diseases
involving the arteries,
veins, or lymphatic
vessels
849
Laparoscopy
761
Hysteroscopy
759
Appendectomy
752
Vascular surgery
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Related Questions
SQL Help
arrow_forward
database
arrow_forward
Chevening Scholarship Database's stores data about each application received by the Chevening Foundation. As a database designer,
you're assigned to design the new database system according to the following requirements:
Each applicant can submit one or more applications (one application per year). Each application form is submitted by only one applicant.
Each application has a unique application ID number, application date, class level, GPA, GPA scale, academic honours, extracurricular activities,
community activities, other scholarship awarded and financial need.
The applicant's record contains student ID, student's last name, first name, date of birth, address, city, state, zip code, phone number and email address.
Each school can be associated with one or more applications. Each application is associated with only one school. Each school has a unique school ID,
school name, type, address, city, zip code and phone number.
Each application must include two or more recommendations.…
arrow_forward
Task 1:Create a database Entity and Relationship Diagram (ERD) for the following application:Please include answer with detailed explanationKhair Application:
The Application idea: It is a mobile application called (Khair) to help people. This assistance is moral, material. This assistance is also provided by good people in our community.
Example to understand the Application idea more: For example, a person urgently needs a medical chair or bed and does not have the ability to purchase what he needs. The medical chair or bed is requested through the application, and whoever has what he requests initiates the request through the application’s messages and determines the meeting place.
Application models (page):
Login included:
login
registration
login included:
user name
password
New registration included:
name
E-mail
Password
phone number
date
National Identification Number
Home page included:
Request - applicant name - address - send message
Messages list form included:
list of…
arrow_forward
(a) List all employees.
Relational Algebra(RA):
Tuple Relational Calculus(TRC):
Domain Relational Calculus(DRC):
(b) List all details of employees who are female.
Relational Algebra(RA):
Tuple Relational Calculus(TRC):
Domain Relational Calculus(DRC):
arrow_forward
Table: Task
Primary key: TaskID
Foreign key: Team MemberID
TaskID
1
2
3
Description
Build login screen
Implement inventory
management
Add logo to splahs 1
screen
Duration StartDate
2022-05-06
4
10
TeamMemberID
2
1
2022-05-15 1
2022-05-06 2
Write a SQL statement that will add the below row to the Task table.
TaskID
Description
Duration
StartDate Team MemberID
3
4
Implement
2022-05-07
email
notifications
Write a SQL statement that will change the value of the Task
description with ID 3 to "Add logo to splash screen".
arrow_forward
A DreamHome database has following tables.
Branch (branchNo, street, city, postcode)
Staff (staffNo, fName, lName, position, sex, DOB, salary, branchNo)
PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo)
PrivateOwner (ownerNo, fName, lName, address, telNo)
How many rows and columns are there in the relation Branch X Staff, if Branch has 20 tuples and Staff has 15 tuples.
( ) 35 rows and 300 columns
( ) 300 rows and 35 columns
( ) 300 rows and 12 columns
( ) 35 rows and 12 columns
arrow_forward
True and falseLogical level is the physical level representation of database true and false mein bataiye
arrow_forward
TASK:
Examples of the UNION operator, the use the DISTINCT operator.
TOPIC : DATABASE
arrow_forward
DailyCases
* Day
PCRTests
9 TestNo
Patientid
TodalTestCount
DateofTest
PositiveTestCount
Result
NegativeTestCount
Tester
DateofResult
Department
TotalCases
* Dey
DepartmentName
TotalTestCount
TotaPositiveCount
Patient
* Patientid
Employee
* Empoyeeld
FirstName
LastName
Title
IdentityNumber
FirstName
RegisteredDate
LastName
BloodGroup
Department
CurrentStatus
StartDate
HesCode
Figure 1
According the database design given in Figure 1;
Write a SQL statement to display PCR test results as below (Figure 2).
First Name of Patient Last Name of Patient Date of Test
Ttle of Tester Name of Tester Date of Result
Resut
DİŞÇİ
Prof Dr.
Eren Bakız
2021-01-20 00:00:00.000 Pending
VOLKAN
2021-12-01
YILDIZ
MUHAMMET EMİN TUNÇ
OZALP
2
SAFA
2021-12-01
Dr.
Bartu Olay
2021-01-21 00:00.00.000 Pendng
3
2021-12-01
Prof Dr.
Eren Bakız
2021-01-18 00 00-00.000 Pending
2021-01-12
Bartu Olay
Bartu Olay
HILAL
Dr.
2021-01-18 00:00.00.000 Pendng
BURAK
AŞRAK
2021-01-12
Dr.
2021-01-20 00:00:00.000 Positive
6
ALI TAHA…
arrow_forward
create an ER table
arrow_forward
Task 12: List the item ID, description, and category for each pair of items that are in the same category. (For example, one such pair would be item FS42 and item PF19, because the category for both items is FSH.) Order the output by category.
arrow_forward
LAB DBI DATABASE
arrow_forward
Create the database name Company which consists of one Entity name Employee which consists of 10 attributes
(ID/First Name/Last Name/CNIC/Gender/Department/Salary/Address/Age)
ID should be primary key
Insert at least 10 records in the Entity Employee with respect to their type.
Implementation the example of stored procedure now creates the stored procedure which:
Have an ability to insert the record in the table.
Have an ability to delete the record in table.
Have an ability to update the record in the table.
Have an ability to display the record in the table.
arrow_forward
Task 1:Create a database Entity and Relationship Diagram (ERD) for the following application (Submitted An image of the Diagram By using Any Application:Application:
The Application idea: It is a mobile application called (Khair) to help people. This assistance is moral, material. This assistance is also provided by good people in our community.
Example to understand the Application idea more: For example, a person urgently needs a medical chair or bed and does not have the ability to purchase what he needs. The medical chair or bed is requested through the application, and whoever has what he requests initiates the request through the application’s messages and determines the meeting place.
Application models (page):
Login included:
login
registration
login included:
user name
password
New registration included:
name
E-mail
Password
phone number
date
National Identification Number
Home page included:
Request - applicant name - address - send message
Messages list form included:
list…
arrow_forward
Task Description: consider the following relations for a university:
Student
(sid: integer, sname: string, GPA: real, dateOfBirth: date)
Department (did: integer, dname: string, capacity: integer)
Course
(cid: integer, cname: string, credit: integer)
Enroll
(studentID: integer, courselD: integer, departmentID: Integer, enrollmentDate: date)
studentlD, courselD, and departmentID in Enroll are foreign keys referencing the primary keys of the
student, course, and department relations, respectively.
arrow_forward
Observe the table ENROLLMENT:
ENROLLMENT
StudentID
StudentName MajorID MajorName
111
Joe
E
English
222
Bob
H
History
333
Lisa
H
History
Which of the following (regarding the relation ENROLLMENT) represents the modification anomaly?
O If a major name changes, it may have to be changed in more than one record
O If a student decides to use a longer version of his or her name, we cannot accommodate that modification
O If a student ID changes, we may have to change the major ID as well
O If a major ID changes, we may have to change the student ID as well
O All of these answers
arrow_forward
Subject: DatabaseQuestion: Draw an ERD based on the given info
arrow_forward
Observe the table ENROLLMENT:
ENROLLMENT
StudentID
StudentName MajorID MajorName
111
Joe
E
English
222
Bob
H
History
333
Lisa
H
History
Which of the following (regarding the relation ENROLLMENT) represents the deletion anomaly?
O Deleting a major ID may delete the major name
O Deleting a major may delete all information about a student
O None of the answers
O Deleting a student ID may delete a student name
O Deleting a student may delete all information about a major
arrow_forward
Database Foundations:
Design a database to produce the following reports. Do not use any surrogate keys in your design.
For each guide, list the guide number, guide last name, guide first name, address, city, state, postal code, telephone number, and date hired.
For each trip, list the trip ID number, the trip name, the location from which the trip starts, the state in which the trip originates, the trip distance, the maximum group size, the type of trip (hiking, biking, or paddling), the season in which the trip occurs, and the guide number, first name, and last name of each guide. A guide may lead many trips and a trip may be led by many different guides.
For each client, list the client number, client last name, client first name, address, city, state, postal code, and telephone number
arrow_forward
Assignment Brief:
Some of the most difficult decisions that you face as a database developer are what tables to
create and what columns to place in each table, as well as how to relate the tables that you
create. Normalization is the process of applying a series of rules to ensure that your database
achieves optimal structure. Normal forms are a progression of these rules. Each successive
normal form achieves a better database design than the previous form did. Based on your
understanding for the various normal form respond to the following problems and provide the
optimal answer/solution.
Problem#1: 1st Normal Form (1NF)
Consider the Students table, with the primary key underlined, and the following data:
Students:
Alpha
100111
Email
doe@usna.edu
Name
Courses
GradePoints
NN204, SI204,
IT221
SM223, EE301
SI204
John Doe
2,3,3
Matt Smith
092244
113221
smith@usna.edu
black@usna.edu
4,4
Melinda
3
Black
NN204, SI204,
IT221
090112
Tom Johnson
Johnson@usna.ed
4,2,3
u
a) Is the Students table…
arrow_forward
using the design file in the image attached to the question :
Normalize the database in the design file
Be aware of the possibility of duplicate data.
Name in Role and Employee and Emp_Contact_name all refer to the same data.
Name and Email in Vendor refer to the Vendor.
Assumptions made:
An employee works with one or more vendors.
An employee may have none or many dependents.
An employee may have many roles over the employee's work history and a record should be retained for each role.
arrow_forward
using the design file in the image attached to the question :
Normalize the database in the design file
Be aware of the possibility of duplicate data.
Name in Role and Employee and Emp_Contact_name all refer to the same data.
Name and Email in Vendor refer to the Vendor.
Assumptions made:
An employee works with one or more vendors.
An employee may have none or many dependents.
An employee may have many roles over the employee's work history and a record should be retained for each role.
Make sure to have a primary key for each table .
Make sure to include the relationships between the tables .
arrow_forward
Project Synopsis:
database management ABC Walk-in Clinic is located in a large metropolitan city in Canada. The clinic staff consists of ten doctors, six nurses, five office secretaries, two administrative assistants and one manager. First time Patients have to visit the clinic personally and fill a registration form that contains their personal and health related information. An office secretary would then enter that information in the computer based information system.
Patients may become a permanent patient (at any time) for one of the doctors at the clinic by filling up necessary forms (they are called enrolled patients) or they may choose to come walk-in for every visit. (They usually called walk-in patients) Enrolled Patients may book their appointments online or by calling, the office and one of the secretaries would then book their appointment with their doctor on a particular day/time. Any booked appointment may be cancelled up to 24 hours in advance after which the clinic…
arrow_forward
Task 1: ER diagram for give scenario
Consider building a database for a shopping mall with requirements as follows:
Each product of the shopping mall has a unique project No, and also has a name, a supplier name, a category, a purchase price and a selling price.
Products need to be supplied from suppliers. Each product has one supplier, but a supplier may supply many products for the shopping mall.
Each supplier has a supplier name and address.
A purchase order for product from a supplier has a unique order No, and a date. The order specifies one or more products and their quantities required for a product.
A sales order for product to customers has a unique order No, and a date. The order specifies one or more products and their quantities required for a product
The shopping mall has warehouses. Each warehouse has a warehouse number, an address and a contact phone number. Each warehouse has shelves to store products. Each shelf has a shelf-number and a capacity. The shelf…
arrow_forward
Hotel(hID, hName, city) primary key is hID Guest(gID, gName, gAddress) primary key is gID
Room(hID, rNumber, price) primary key is (hID, rNumber). hID is a foreign key to Hotel relation
Book(gID, hID, rNumber, StartDate, EndDate) primary key is all attributes! (hID, rNumber) is a
foreign key to Room relation gID is a foreign key to Guest relation. Write in relational algebra
the following queries: Select guests who stayed at least 1 day in New York City hotels between
September 8, 2001, and September 11, 2001. Select guests who have never booked a room in
the same hotels that John Doe has. Select guests who have booked a room in every hotel in
Las Vegas.
arrow_forward
SEE MORE QUESTIONS
Recommended textbooks for you
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Programming Logic & Design Comprehensive
Computer Science
ISBN:9781337669405
Author:FARRELL
Publisher:Cengage
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Related Questions
- SQL Helparrow_forwarddatabasearrow_forwardChevening Scholarship Database's stores data about each application received by the Chevening Foundation. As a database designer, you're assigned to design the new database system according to the following requirements: Each applicant can submit one or more applications (one application per year). Each application form is submitted by only one applicant. Each application has a unique application ID number, application date, class level, GPA, GPA scale, academic honours, extracurricular activities, community activities, other scholarship awarded and financial need. The applicant's record contains student ID, student's last name, first name, date of birth, address, city, state, zip code, phone number and email address. Each school can be associated with one or more applications. Each application is associated with only one school. Each school has a unique school ID, school name, type, address, city, zip code and phone number. Each application must include two or more recommendations.…arrow_forward
- Task 1:Create a database Entity and Relationship Diagram (ERD) for the following application:Please include answer with detailed explanationKhair Application: The Application idea: It is a mobile application called (Khair) to help people. This assistance is moral, material. This assistance is also provided by good people in our community. Example to understand the Application idea more: For example, a person urgently needs a medical chair or bed and does not have the ability to purchase what he needs. The medical chair or bed is requested through the application, and whoever has what he requests initiates the request through the application’s messages and determines the meeting place. Application models (page): Login included: login registration login included: user name password New registration included: name E-mail Password phone number date National Identification Number Home page included: Request - applicant name - address - send message Messages list form included: list of…arrow_forward(a) List all employees. Relational Algebra(RA): Tuple Relational Calculus(TRC): Domain Relational Calculus(DRC): (b) List all details of employees who are female. Relational Algebra(RA): Tuple Relational Calculus(TRC): Domain Relational Calculus(DRC):arrow_forwardTable: Task Primary key: TaskID Foreign key: Team MemberID TaskID 1 2 3 Description Build login screen Implement inventory management Add logo to splahs 1 screen Duration StartDate 2022-05-06 4 10 TeamMemberID 2 1 2022-05-15 1 2022-05-06 2 Write a SQL statement that will add the below row to the Task table. TaskID Description Duration StartDate Team MemberID 3 4 Implement 2022-05-07 email notifications Write a SQL statement that will change the value of the Task description with ID 3 to "Add logo to splash screen".arrow_forward
- A DreamHome database has following tables. Branch (branchNo, street, city, postcode) Staff (staffNo, fName, lName, position, sex, DOB, salary, branchNo) PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo) PrivateOwner (ownerNo, fName, lName, address, telNo) How many rows and columns are there in the relation Branch X Staff, if Branch has 20 tuples and Staff has 15 tuples. ( ) 35 rows and 300 columns ( ) 300 rows and 35 columns ( ) 300 rows and 12 columns ( ) 35 rows and 12 columnsarrow_forwardTrue and falseLogical level is the physical level representation of database true and false mein bataiyearrow_forwardTASK: Examples of the UNION operator, the use the DISTINCT operator. TOPIC : DATABASEarrow_forward
- DailyCases * Day PCRTests 9 TestNo Patientid TodalTestCount DateofTest PositiveTestCount Result NegativeTestCount Tester DateofResult Department TotalCases * Dey DepartmentName TotalTestCount TotaPositiveCount Patient * Patientid Employee * Empoyeeld FirstName LastName Title IdentityNumber FirstName RegisteredDate LastName BloodGroup Department CurrentStatus StartDate HesCode Figure 1 According the database design given in Figure 1; Write a SQL statement to display PCR test results as below (Figure 2). First Name of Patient Last Name of Patient Date of Test Ttle of Tester Name of Tester Date of Result Resut DİŞÇİ Prof Dr. Eren Bakız 2021-01-20 00:00:00.000 Pending VOLKAN 2021-12-01 YILDIZ MUHAMMET EMİN TUNÇ OZALP 2 SAFA 2021-12-01 Dr. Bartu Olay 2021-01-21 00:00.00.000 Pendng 3 2021-12-01 Prof Dr. Eren Bakız 2021-01-18 00 00-00.000 Pending 2021-01-12 Bartu Olay Bartu Olay HILAL Dr. 2021-01-18 00:00.00.000 Pendng BURAK AŞRAK 2021-01-12 Dr. 2021-01-20 00:00:00.000 Positive 6 ALI TAHA…arrow_forwardcreate an ER tablearrow_forwardTask 12: List the item ID, description, and category for each pair of items that are in the same category. (For example, one such pair would be item FS42 and item PF19, because the category for both items is FSH.) Order the output by category.arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning
- Programming Logic & Design ComprehensiveComputer ScienceISBN:9781337669405Author:FARRELLPublisher:CengageA Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Programming Logic & Design Comprehensive
Computer Science
ISBN:9781337669405
Author:FARRELL
Publisher:Cengage
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr