A spreadsheet was used to keep data for the booking system of the ABC Clinic, with sample data shown in Table 1. Columns 1-4 contain information for doctors, Columns 5-9 contain information for patients and the last 3 columns contain information for patient’s appointments with doctors. The spreadsheet for the table is also available on Canvas.
With more and more patients and doctors, the ABC Clinic decides to go for database system to manage data. You are asked to design a relational database to keep data to answer clinic operation queries such as:
1.1. (4 marks) A database schema of one relation as shown below is proposed, where semantics of attributes are self-explanatory. Discuss at least two weaknesses of this design.
ABC(doc-name, doc-gender, registration_num, qualification, pat-name, pat-gender,
DOB, address, phone-num, appoint-date, appoint-time, type)
1.2. (6 marks) Propose your own design for the database. Your design must be able to keep all information shown in Table 1. Explain your design. Give the schema for each relation in your database and specify their data integrity constraints (underline primary keys and put asterisk for foreign keys).
According to your design for the ABC database in Question 1.2, complete below tasks to populate your database with the data shown in Table 1.
2.1. (5 marks) Give the CREATE TABLE statement for each relation, including primary key and any foreign key constraints.
2.2. (5 marks) Give the INSERT INTO statements for each relation in your design. All data shown in Table 1 must be inserted into your database.
In addition to the lecture notes, you should also study by yourself the SQL*Plus tutorial on Canvas (the Oracle section) and other resources for Oracle syntax and useful functions.
The ER model for the Academics database is as follows:
The relational schema for the Academics database is as follows:
DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode)
ACADEMIC(acnum, deptnum*, famname, givename, initials, title)
FIELD(fieldnum, id, title)
INTEREST(fieldnum*, acnum*, descrip)
Some notes on the Academics database:
Download and run the SQL script academics.sql on Canvas (the Oracle section) to define and populate the Academics database in your Oracle account.
Write ONE SQL query for each of questions 3.1–3.8, and each component of an SQL statement must be on a separate line. Your query should not produce duplicates in output but use DISTINCT only if necessary. Include answers for Questions 3.9 and 3.10 as comments starting with “//” to make an SQL script executable in SQL Developer.
Each question below is worth 3 marks.
3.1. List the names of all the institutions (instname) currently in the database.
3.2. How many academics are there in the department where deptnum=100. Return the total number.
3.3. List in alphabetical order the family name (famname) and given name (givename) of academics who has a title.
3.4. List papers whose title contains the words “Data” or “Software”, in upper or lower cases. List the panumand titleof these papers.
3.5. Return the panumof papers written by the academic “Mark Yee”.
3.6. Return the famnameand givenameof academics whose acnumis in the range [100..199] or whose givenamestarts with “S”.
3.7. List the panumof papers having at least two authors.
3.8. List in alphabetical order the famname, givenameof academics who work for institutions in Queensland. Note: the value of Queensland in the database is QLD or Qld.
3.9. The below query is intended to list the fieldnum and title of fields whose fieldnum is between 500 and 599 or whose title contains the word ‘Data’. But it has errors. Give the correct SQL query. select fieldnum, title from field where fieldnum >=500 and <=599 or upper(title) like ‘Data%’;
3.10. Describe in English what the output will be from the SQL query below. Do not give a literal description of each line of the query. select acnum, field.fieldnum, title, descrip from field, interest where field.fieldnum=interest.fieldnum and trim(ID) like ‘B.1._’
TXT is a logistics and transport company. You are asked to design a database for scheduling their transport jobs. Requirements for the database are as follows:
According to the given description, state any assumptions you make and construct an Entity Relationship (ER) diagram for the database. You must represent entities, relationships and their attributes, and all applicable constraints in your ER diagram. Explain any concepts in the description that cannot be expressed in the ER diagram.
Designing an ER diagram is the first step for database design. It is critical that we understand what makes a good design. Figure 2 is an ER diagram designed for the Orders database about customers and their orders for a company. The semantics of attributes, entity sets and relationships is as expressed in their names. Each item in the database must have an “owning” salesman, whether or not it is being ordered.
5.1. (5 marks) Discuss any issues with the given ER diagram. Give your suggested ER diagram.
5.2. (15 marks) Map your suggested ER diagram to a relational database schema. For each relation schema in the mapped relational database schema, indicate the primary key (underline) and any foreign keys (asterisk).
Buy Online Assignment Help services for COMPUTER ASSIGNMENT with Punjab Assignment Help at email@example.com