country
$20 Bonus + 25% OFF
Securing Higher Grades Costing Your Pocket? Book Your Assignment at The Lowest Price Now!
Add File

Error goes here

ISYS1055 Database Concepts

tag 0 Download1 Pages / 144 Words tag Add in library Click this icon and make it bookmark in your library to refer it later. GOT IT
  • Course Code: ISYS1055
  • University: Australian Institute Of Higher Education
  • Country: Australia

Question:

A spreadsheet was used to keep data for the booking system of the ABC Clinic, with sample data hown 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.
Table 1. Data for the ABC Clinic
With more and more patients and doctors, the ABC Clinic decides to go for database system to anage data. You are asked to design a relational database to keep data to answer clinic operation queries such as:

? List the patient appointments for each doctor for a given date.
? When a patient rings to make an appointment, give the available time slots for a given date.
? Retrieve the address of patients to send notices via mail services.
Answer questions:
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).

Question 2. Relational model -- SQL DDL and DML (10 marks)
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 oreign key constraints.
2.2. (5 marks) Give the INSERT INTO statements for each relation in your design. All data shown in able 
must be inserted into your database.
 
Question 3. SQL (30 marks).
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)
PAPER(panum, title) AUTHOR(panum*, acnum*) FIELD(fieldnum, id, title) INTEREST(fieldnum*, acnum*, descrip) Some notes on the Academics database:
? An academic department belongs to one institution (instname) and often has many academics. An
academic only works for one department.
? Research papers (PAPER) are often authored by several academics, and of course an academic often
writes several papers (AUTHOR).
? A research field (FIELD) often attracts many academics and an academic can have interest (INTEREST) in several research fields.
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 e 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.
4
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
panum and title of these papers.
3.5. Return the panum of papers written by the academic “Mark Yee”.
3.6. Return the famname and givename of academics whose acnum is in the range [100..199] or whose givename starts with “S”.
3.7. List the panum of papers having at least two authors.
3.8. List in alphabetical order the famname, givename of 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._';

Question 4. ER model (30 marks).
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:
? Each truck has a unique registration No, and is also described by type, and year made.
? Each customer of TXT has a unique ABN and is also described by the name, and contact phone number.
? Delivery jobs are identified by the unique job No, and are also described by “from” and “to”
destinations, and estimated distance to travel.
? Drivers are identified by the unique employee ID, and are also described by first name, last name,
address and phone number.
? A customer may book in several delivery jobs and a job is from only one customer. A job must have an “owning” customer.
? A driver can be assigned several delivery jobs but a job has only one driver. Some description about the
job is also recorded. Each job has a Status (eg open, complete,etc) and this is maintained in the database.
? A job is assigned only one truck but a truck can be assigned to several jobs. 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.
? Your ER diagram must only use notations from the lecture notes and must not be hand drawn. ER diagrams
using other notations will receive zero mark for this question.
? You can use the ER diagramming tool Dia, which can be downloaded from Canvas (The Tools section) and
is also available as an App on mydesktop.rmit.edu.au. When exporting your ER diagram in Dia to a pdf file,
you need to first set “page setup” to “Fit to 1 by 1” so that your pdf diagram scales properly. You can also use any other diagramming tool.
 
Some common errors in ER diagrams:
? If the scenario is set in say, a club, you do not need an entity for the club. The arrangement you depict in
the ER diagram is the club.
? Entities do not connect to other entities with straight lines. Entities have relationships to/with other entities
so there has to be a relationship between connected entities.
? Every entity must have a primary key.
? The names of all relationships and entities must be unique.
? There are no foreign keys in ER diagrams. They come about as part of mapping an ER model into a
relational schema.
 
Question 5. Relational database design. (20 marks).
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). 
Download Sample Now

Earn back the money you have spent on the downloaded sample by uploading a unique assignment/study material/research material you have. After we assess the authenticity of the uploaded content, you will get 100% money back in your wallet within 7 days.

Upload
Unique Document

Document
Under Evaluation

Get Money
into Your Wallet

Total 1 pages, 1 USD Per Page

Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2020). Database Concepts. Retrieved from https://myassignmenthelp.com/free-samples/isys1055-database-concepts/relation-and-develop.html.

My Assignment Help (2020) Database Concepts [Online]. Available from: https://myassignmenthelp.com/free-samples/isys1055-database-concepts/relation-and-develop.html
[Accessed 17 January 2021].

My Assignment Help. 'Database Concepts' (My Assignment Help, 2020) <https://myassignmenthelp.com/free-samples/isys1055-database-concepts/relation-and-develop.html> accessed 17 January 2021.

My Assignment Help. Database Concepts [Internet]. My Assignment Help. 2020 [cited 17 January 2021]. Available from: https://myassignmenthelp.com/free-samples/isys1055-database-concepts/relation-and-develop.html.


For years, MyAssignmenthelp.com has been operating as one of the cheap assignment help providers in the USA. We are one of the best college paper writing services that keep service price minimal. We do not let the affordability of our service to hamper the standard of our work. We have separate teams of experts to provide report writing help . At MyAssigenmnthelp.com, we believe in earning credibility, so students can pay for assignments only after getting satisfied solutions. Tough assignments bother you? Trust us with your project. You will not regret paying us to write assignments for you.

Latest Database Samples

CS665 Software Design And Patterns

Download : 0 | Pages : 2

Answer: My implementation has achieved a high degree of simplicity and understandability as a result of using objects to represent different objects specified in the requirements. Each object has its own methods and attributes. The objects interact with each other to form the complete program which is easy to understand because it is modular based thus it’s easy to understand. The solution to be implemented will be flexible because each ...

Read More arrow

ICT701 Relational Database Systems

Download : 0 | Pages : 8

Answer: Normalization is done through three steps; 1NF- step involves identifying any repeating groups and removing to normalize the table to 1NF. 2NF- This step involves removing any partial dependency in all relations. 3NF- This step involves removing any transitive dependency in all the relations. Although there are other levels of normalization, 3NF is the recommended level before implementing the entities as tables to form a database...

Read More arrow

NIT1201 Introduction To Database Systems

Download : 0 | Pages : 12
  • Course Code: NIT1201
  • University: Victoria University
  • Country: Australia

Answer: ID -  used to identify the primary keys and the foreign keys in the tables. For instance Match_ID, Round_ID, Ref_ID and Player_ID  are both primary keys and foreign keys in different tables. Round – is the game level or cycle. The Tournament has four levels which translate to four rounds. Role – is found in the player table. There are only three roles a player can partake in a team; leadership, being a primary p...

Read More arrow

ICT704 Non Relational Database Systems

Download : 0 | Pages : 5

Answer: We have a very simple database. There is one collection movies which contain a number of Movie documents. Each movie documents has a number of attributes which defines various attributes of movies. Mongo Db has a very important property. Unlike relational databases where data fields are empty if they don't have any value, Mongo DB do not even have a field if it does not have any value. We can simply skip that field from a document,. So...

Read More arrow

COMP3340 Data Mining

Download : 0 | Pages : 2
  • Course Code: COMP3340
  • University: The University Of Newcastle
  • Country: Australia

Answer: Introduction Web mining could be defined as the process of using the techniques of data mining and several algorithms in order to extract direct information form the internet. The primary aim of web mining is to search for patterns within the web based data (Langhnoja, Barot & Mehta, 2013). Discussion Real World Examples of Web Mining The results obtained from the usage of web mining could be applied in order to understand and ...

Read More arrow
Next

Save Time & improve Grade

Just share requirement and get customized Solution.

watch
question
We will use e-mail only for:

arrow Communication regarding your orders

arrow To send you invoices, and other billing info

arrow To provide you with information of offers and other benefits

Add File

Error goes here

1,544,311

Orders

4.9/5

Overall Rating

5,108

Experts

Our Amazing Features

delivery

On Time Delivery

Our writers make sure that all orders are submitted, prior to the deadline.

work

Plagiarism Free Work

Using reliable plagiarism detection software, Turnitin.com.We only provide customized 100 percent original papers.

time

24 X 7 Live Help

Feel free to contact our assignment writing services any time via phone, email or live chat. If you are unable to calculate word count online, ask our customer executives.

subject

Services For All Subjects

Our writers can provide you professional writing assistance on any subject at any level.

price

Best Price Guarantee

Our best price guarantee ensures that the features we offer cannot be matched by any of the competitors.

Our Experts

Assignment writing guide
student rating student rating student rating student rating student rating 5/5

440 Order Completed

99% Response Time

Jack Arens

MBA in HRM

London, United Kingdom

Hire Me
Assignment writing guide
student rating student rating student rating student rating student rating 5/5

1692 Order Completed

98% Response Time

Alfred Dodd

PhD in Computer and Information Science with specialization in Database

Wellington, New Zealand

Hire Me
Assignment writing guide
student rating student rating student rating student rating student rating 5/5

529 Order Completed

95% Response Time

Ivan Blank

PhD in Functional Human Biology

Wellington, New Zealand

Hire Me
Assignment writing guide
student rating student rating student rating student rating student rating 4/5

248 Order Completed

100% Response Time

Lloyd Bernabe

MSc in Accounting

London, United Kingdom

Hire Me

FREE Tools

plagiarism

Plagiarism Checker

Get all your documents checked for plagiarism or duplicacy with us.

essay

Essay Typer

Get different kinds of essays typed in minutes with clicks.

edit

GPA Calculator

Calculate your semester grades and cumulative GPa with our GPA Calculator.

referencing

Chemical Equation Balancer

Balance any chemical equation in minutes just by entering the formula.

calculator

Word Counter & Page Calculator

Calculate the number of words and number of pages of all your academic documents.

Refer Just 5 Friends to Earn More than $2000

Check your estimated earning as per your ability

1

1

1

Your Approx Earning

Live Review

Our Mission Client Satisfaction

Good completion to my assignment.  My assignment editing was perfect, and I was very happy with that.  When I requested the expert to follow all the comments, he responded in perfect way.  The assignment was completed on the deadline and to my ...

flag

User Id: 394056 - 17 Jan 2021

Australia

student rating student rating student rating student rating student rating

The writer finished before the due date and the final result was excellent, happy with this one.

flag

User Id: 406130 - 17 Jan 2021

Australia

student rating student rating student rating student rating student rating

Excellent work I really appreciate about your work related to my entire assignments and hopefully i can contact you all again once i receive my final work from my university. Again i will take this opportunity to thanking you about all the supports...

flag

User Id: 573225 - 17 Jan 2021

Australia

student rating student rating student rating student rating student rating

The expert really proficient in their work and really helpful to your academic work.

flag

User Id: 587834 - 17 Jan 2021

Australia

student rating student rating student rating student rating student rating
callback request mobile
Have any Query?