CHAPTER 1: DATABASES AND DATABASE USERS
Doctor (DocID: string, DName: string, DAddress: string,
Numpatients: integer)
Treats (DocId: string, SSN: string, illness: string, day: string, cost: real)
Patient (SSN: string, PatientName: string, PAddress: string, Age:integer)
Note : DocID, DName, DAddress and Numpatients stand for the
doctor’s identifier, name, address and the total number of patients
the doctor has, respectively. Also, SSN, PatientName, PAddress and
Age stand for the the patient’s social security number, name,
address and age respectively. The rest of the attributes are for
the illness (eg. Cold, flu, diabetes, headache) and cost of each
treatment visit (eg. $85) not including cost of buying
prescription. Note that a doctor can treat a patient for several
illnesses.
Solution : (10 marks for que 1)
Query |
Answer |
Files involved |
(3 marks) |
An instance of the Doctor-Treats-Patient database is : Doctor Patient Treats |
|
2. Provide 2 informal English queries
from this database with their answers. Each query should use at
least 2 of the files in the database and your answer should
indicate the file (e.g., Doctor, Treats) needed to answer each
query and specify what fields are being retrieved as the result
(e.g., DName, cost). |
English Queries are :
ii. Result of queries :
ii. |
|
3. Specify at least 3 relationships
(one for each of the 3 database files) among the records of the
database. For each file (e.g., Doctor), list any relationships this
file (eg. Doctor) has with the other files through its fields
(e.g., DocID). |
Solution : (5 marks for que 2i)
(a)
|
ii) Give 5 examples of integrity constraints that you think can apply to the Doctor-Treats-Patient database database of question 1. (5 marks)
Solution : (5 marks for que 2ii)
I have included all the key definitions of DBMS important terms that are required for an adequate explanation of answer for example integrity constraints and its types, queries, schema, instances, etc.
CHAPTER 1: DATABASES AND DATABASE USERS Given the simple Doctor-Treats_Patient database schema which contains three files...
1. Shown below is the relational database schema for a database which contains details regarding bus journeys from Tronoh to various destinations: BusOperator (opCode, opName) Journey(QpCode,GestinationCode price) Destination (destinationCode, destinationName, distance) a. Formulate the following queries using relational algebra: . Get the name of bus operators (e.g., Plusliner, Sri Maju etc.) that provide journeys which are priced at more than RM50.00. (3 marks ii. List the names of a destinations along with their prices provided by Plusliner. 3 marks] b....
Topic: Inventory Manangement Part A. Database Creation Your database should have a minimum of 4 tables and include both one-to-many and many-to-many relationships. Be sure to include some numeric and/or date fields. Define all appropriate constraints, using the proper naming conventions (see Structure Notes below). Populate your database with at least 30 records in the main table(s), and whatever is needed in related tables. Submit the following: • a short description of the purpose of the database and what the...
This is about database system. Thank you. Question A1 You are given with three Oracle tables, namely, Program, Channel and Booking, as shown in Tables 1 to 3. PROGRAM_ID PROGRAM_NAME DURATION 2012022 Dragon Dance 2014063 Haunted House 2016005 CID 2017172 Kung Fu Master 2018322 Family Affair 2019006 Hong Kong Sites 2019113 2019 Singing Contest 25 58 45 28 68 120 75 Table 1: Program CHANNEL_NO CHANNEL_NAME TVR-1 Rediffusion Channel 1 TV-P Television Pearl Channel TV-G Television Gold Channel Table 2:...
484 Core Concepts of Accounting Information Systems a. Using Access, create an employee table sim- ilar to the one shown in Figure 15-4. Create created. (Hint You can use the aforemen- tioned expression for the department code as a validation rule.) at least one record for this table b. Create validation rules for the listed restric tions and an accompanying validation text for each. Document your work by provid- ing a table outlining exactly what rules you c. Test each...
The lab for this week addresses taking a logical database design (data model) and transforming it into a physical model (tables, constraints, and relationships). As part of the lab, you will need to download the zip file titled CIS336Lab3Files from Doc Sharing. This zip file contains the ERD, Data Dictionary, and test data for the tables you create as you complete this exercise. Your job will be to use the ERD Diagram found below as a guide to define the...