#givename

[sinenasblog]

😄 Give the name to this "angel".😉#givename #myart #oc#characterdesign #colorart #dailyphoto #watercolorpainting #artsy #werewolf #artbook #instagramart #sketchy #blackart #draw #imartist #drawing #cool#coolart #handsomeboys #doodlelove #handsomeboy (at Russia)

[eirenically]

There are 574 federally recognized Native American tribes across the U.S., and while the numbers vary from state to state and tribe to tribe, it is becoming clear that Native Americans are being hit harder by the coronavirus than the overall population. Loretta Christensen, the Navajo area chief medical officer at Indian Health Service, which serves as the federal health program for American Indians and Alaska Natives, observes “In essence, we’re asking people without running water to wash their hands several times a day. We’re asking people without the funds necessary to buy two weeks of food, stay home. Don't go out and shop. And then we’re saying socially distance when a lot of our families, our multigenerations (ph), live in one small home.” The United Methodist Church includes 149 Native American congregations, 21,000 Native Americans are United Methodists. There is a great need. On this Native American Ministries Sunday, please consider helping out. You can give online at UMCgiving.org/giveNAMS or mail checks to: Native American Ministries Sunday - GCFA, P.O. Box 340029, Nashville, TN 37203. https://www.instagram.com/p/B_c_ODLAjUw/?igshid=85tqgt6i6a9e

[hwlanceassignments-blog]

Embry MATH 211 all weeks Assignments

Follow Below Link to Download File

https://homeworklance.com/downloads/pcn-644-evaluation-mental-emotional-status-entire-course/

We also Do 100% Original and Plagiarism Free Assignment / Homework and Essay

Email us for original and Plagiarism Free Work At ( info.homeworklance@gmail.com ) or order us at (https://homeworklance.com/custom-order/ )

Embry MATH211 Module 3 Assignment 1 latest Submit your StatCrunch Assignment 1 by uploading your assignment file that was assigned in Module 2 Your work should demonstrate the results of your random sampling of the data you collected on the StatCrunch U students Submit your StatCrunch Assignment 2 by uploading your assignment file Your work should demonstrate the results and answers for the bootstrapping and randomization assignment Submit your StatCrunch Assignment 3 by uploading your assignment file Your work should demonstrate the results of the population proportions work you did with StatCrunch data Review the rubric and your project for any final adjustments or edits When you are ready, submit your t-Test Projecthere It will be screen by Turnitin, an anti-plagiarism tool Turnitin is a cloud software tool that compares your writing with a database of millions of other written works and checks for originality versus plagiarized papers You will be able to view your originality report after you have submitted your work However, you will not be permitted to make any changes to your paper Important:The project is a course requirement It is wise to complete the project before the Final Exam After completing the previous activity, you should be ready to complete and submit the t-Test Project Plan form to your instructor To begin the first written part of this project: Choose a research question that can be addressed using a t-test Ensure your research question allows you to collect data to analyze Devise a plan to collect your data Submit a project plan form to the instructor and obtain approval If you have not already, download the t-Test Project Plan document below You should submit it by the third day of this week docx” href=”https://erauinstructurecom/courses/36691/files/5978148/download?wrap=1″>t-Test Project PlanError! Hyperlink reference not validinstructurecom/courses/36691/files/5978148/download?wrap=1″ title=”Preview the document”>instructurecom/images/previewpng” alt=”Preview the document”>Error! Hyperlink reference not validinstructurecom/courses/36691/files/5978148/download?wrap=1″ title=”View in a new window”>instructurecom/images/popoutpng” alt=”View in a new window”> Your instructor will review your plan and indicate if it is accepted by awarding a “Complete” grade Your instructor may also give you feedback at this point If your plan is rejected you will receive feedback and an “Incomplete” grade Go to Grades to review your instructors response If the proposal is rejected, your instructor will give you the rationale and suggestions so you can revise and resubmit your project plan as quickly as possible ProjectPlan:t-Test Name: A Type ofstatistical testyou planto conduct(check one, and giverelevantdetails) ¨1-samplet-test a) Targetpopulation: b) Research variable: c) Research question: d) Expectedresult: ¨matched pairst-test a) Targetpopulation(s): b) Theresearch variable willbe thedifference betweenand c) Research question: d) Expectedresult: ¨2-samplet-test a) Targetpopulations(2): b) Research variable: c) Research question: d) Expectedresult: B Data collection plan(check one,and give relevant details) ¨I will use data from a file Give name of file and details below ¨Iwillobtaindata fromawebsite(s) GiveName of website(s), full URLsand details below ¨Otherdata collection plan:Give specific detailsbelow Projectplanis:NOTAccepted Accepted Date InstructorInitials

[charlottebower1-blog]

Embry MATH 211 all weeks Assignments

Follow Below Link to Download File

 https://homeworklance.com/downloads/pcn-644-evaluation-mental-emotional-status-entire-course/

 We also Do 100% Original and Plagiarism Free Assignment / Homework and Essay

 Email us for original and Plagiarism Free Work At ( info.homeworklance@gmail.com ) or order us at (https://homeworklance.com/custom-order/ )

   Embry MATH211 Module 3 Assignment 1 latest 

Submit your

 StatCrunch Assignment 1

 by uploading your assignment file that was assigned in Module 2

Your work should demonstrate the results of your random sampling of the data you collected on the

StatCrunch U

students

Submit your

 StatCrunch Assignment 2

 by uploading your assignment file

Your work should demonstrate the results and answers for the bootstrapping and randomization assignment

 Submit your

 StatCrunch Assignment 3

 by uploading your assignment file

Your work should demonstrate the results of the population proportions work you did with StatCrunch data

 Review the rubric and your project for any final adjustments or edits When you are ready, submit your t-Test Projecthere

It will be screen by Turnitin, an anti-plagiarism tool Turnitin is a cloud software tool that compares your writing with a database of millions of other written works and checks for originality versus plagiarized papers You will be able to view your originality report after you have submitted your work However, you will not be permitted to make any changes to your paper

Important:The project is a course requirement It is wise to complete the project before the Final Exam

After completing the previous activity, you should be ready to complete and submit the t-Test Project Plan form to your instructor To begin the first written part of this project:

·         Choose a research question that can be addressed using a t-test

·         Ensure your research question allows you to collect data to analyze

·         Devise a plan to collect your data

·         Submit a project plan form to the instructor and obtain approval

If you have not already, download the t-Test Project Plan document below You should submit it by the third day of this week

docx” href=”https://erauinstructurecom/courses/36691/files/5978148/download?wrap=1″>t-Test Project PlanError! Hyperlink reference not validinstructurecom/courses/36691/files/5978148/download?wrap=1″ title=”Preview the document”>instructurecom/images/previewpng” alt=”Preview the document”>Error! Hyperlink reference not validinstructurecom/courses/36691/files/5978148/download?wrap=1″ title=”View in a new window”>instructurecom/images/popoutpng” alt=”View in a new window”>

Your instructor will review your plan and indicate if it is accepted by awarding a “Complete” grade Your instructor may also give you feedback at this point

If your plan is rejected you will receive feedback and an “Incomplete” grade Go to Grades to review your instructors response If the proposal is rejected, your instructor will give you the rationale and suggestions so you can revise and resubmit your project plan as quickly as possible

ProjectPlan:t-Test

Name:

A Type ofstatistical testyou planto conduct(check one, and giverelevantdetails)

¨1-samplet-test

1.     a) Targetpopulation:

2.     b) Research variable:

3.     c) Research question:

4.     d) Expectedresult:

¨matched pairst-test

1.     a) Targetpopulation(s):

2.     b) Theresearch variable willbe thedifference

betweenand

1.     c) Research question:

2.     d) Expectedresult:

¨2-samplet-test

1.     a) Targetpopulations(2):

2.     b) Research variable:

3.     c) Research question:

4.     d) Expectedresult:

 B Data collection plan(check one,and give relevant details)

¨I will use data from a file Give name of file and details below

¨Iwillobtaindata fromawebsite(s) GiveName of website(s), full URLsand details below

¨Otherdata collection plan:Give specific detailsbelow

Projectplanis:NOTAccepted Accepted Date

InstructorInitials

[valeriegwavuya444-blog]

New Post has been published on https://punjabassignmenthelp.com/sys1055-1057-database-concepts-database/

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

COMPUTER ASSIGNMENT HELP

Question 1. The Relational model (10 marks).

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.

Table 1. Data for the ABC Clinic

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:

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 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.

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 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._’

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 isthe 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).

Punjab Assignment Help

Buy Online Assignment Help services for COMPUTER ASSIGNMENT with Punjab Assignment Help at punjabassignmenthelp@gmail.com

[jameswilson53-blog]

New Post has been published on https://punjabassignmenthelp.com/sys1055-1057-database-concepts-database/

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

COMPUTER ASSIGNMENT HELP

Question 1. The Relational model (10 marks).

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.

Table 1. Data for the ABC Clinic

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:

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 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.

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 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._’

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 isthe 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).

Punjab Assignment Help

Buy Online Assignment Help services for COMPUTER ASSIGNMENT with Punjab Assignment Help at punjabassignmenthelp@gmail.com

[oliversmith63-blog]

New Post has been published on https://punjabassignmenthelp.com/sys1055-1057-database-concepts-database/

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

COMPUTER ASSIGNMENT HELP

Question 1. The Relational model (10 marks).

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.

Table 1. Data for the ABC Clinic

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:

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 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.

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 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._’

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 isthe 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).

Punjab Assignment Help

Buy Online Assignment Help services for COMPUTER ASSIGNMENT with Punjab Assignment Help at punjabassignmenthelp@gmail.com

[metinablachim63-blog]

New Post has been published on https://punjabassignmenthelp.com/sys1055-1057-database-concepts-database/

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

COMPUTER ASSIGNMENT HELP

Question 1. The Relational model (10 marks).

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.

Table 1. Data for the ABC Clinic

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:

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 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.

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 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._’

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 isthe 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).

Punjab Assignment Help

Buy Online Assignment Help services for COMPUTER ASSIGNMENT with Punjab Assignment Help at punjabassignmenthelp@gmail.com

[dianadorman555-blog]

New Post has been published on https://punjabassignmenthelp.com/sys1055-1057-database-concepts-database/

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

COMPUTER ASSIGNMENT HELP

Question 1. The Relational model (10 marks).

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.

Table 1. Data for the ABC Clinic

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:

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 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.

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 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._’

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 isthe 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).

Punjab Assignment Help

Buy Online Assignment Help services for COMPUTER ASSIGNMENT with Punjab Assignment Help at punjabassignmenthelp@gmail.com

[gurnam36-blog]

New Post has been published on https://punjabassignmenthelp.com/sys1055-1057-database-concepts-database/

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

COMPUTER ASSIGNMENT HELP

Question 1. The Relational model (10 marks).

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.

Table 1. Data for the ABC Clinic

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:

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 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.

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 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._’

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 isthe 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).

Punjab Assignment Help

Buy Online Assignment Help services for COMPUTER ASSIGNMENT with Punjab Assignment Help at punjabassignmenthelp@gmail.com

[kayaknight666-blog]

New Post has been published on https://punjabassignmenthelp.com/sys1055-1057-database-concepts-database/

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

COMPUTER ASSIGNMENT HELP

Question 1. The Relational model (10 marks).

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.

Table 1. Data for the ABC Clinic

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:

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 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.

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 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._’

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 isthe 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).

Punjab Assignment Help

Buy Online Assignment Help services for COMPUTER ASSIGNMENT with Punjab Assignment Help at punjabassignmenthelp@gmail.com

[johnmartin63-blog]

New Post has been published on https://punjabassignmenthelp.com/sys1055-1057-database-concepts-database/

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

COMPUTER ASSIGNMENT HELP

Question 1. The Relational model (10 marks).

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.

Table 1. Data for the ABC Clinic

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:

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 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.

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 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._’

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 isthe 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).

Punjab Assignment Help

Buy Online Assignment Help services for COMPUTER ASSIGNMENT with Punjab Assignment Help at punjabassignmenthelp@gmail.com

[elenadumitre333-blog]

New Post has been published on https://punjabassignmenthelp.com/sys1055-1057-database-concepts-database/

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

COMPUTER ASSIGNMENT HELP

Question 1. The Relational model (10 marks).

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.

Table 1. Data for the ABC Clinic

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:

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 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.

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 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._’

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 isthe 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).

Punjab Assignment Help

Buy Online Assignment Help services for COMPUTER ASSIGNMENT with Punjab Assignment Help at punjabassignmenthelp@gmail.com

[mirceacazacu94-blog]

New Post has been published on https://punjabassignmenthelp.com/sys1055-1057-database-concepts-database/

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

COMPUTER ASSIGNMENT HELP

Question 1. The Relational model (10 marks).

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.

Table 1. Data for the ABC Clinic

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:

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 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.

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 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._’

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 isthe 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).

Punjab Assignment Help

Buy Online Assignment Help services for COMPUTER ASSIGNMENT with Punjab Assignment Help at punjabassignmenthelp@gmail.com

[deep94161-blog]

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

New Post has been published on https://punjabassignmenthelp.com/sys1055-1057-database-concepts-database/

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

COMPUTER ASSIGNMENT HELP

Question 1. The Relational model (10 marks).

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.

Table 1. Data for the ABC Clinic

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:

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 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.

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 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._’

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 isthe 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).

Punjab Assignment Help

Buy Online Assignment Help services for COMPUTER ASSIGNMENT with Punjab Assignment Help at punjabassignmenthelp@gmail.com

[monicagill123-blog]

New Post has been published on https://punjabassignmenthelp.com/sys1055-1057-database-concepts-database/

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

SYS1055/1057 | DATABASE CONCEPTS | DATABASE

COMPUTER ASSIGNMENT HELP

Question 1. The Relational model (10 marks).

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.

Table 1. Data for the ABC Clinic

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:

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 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.

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 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._’

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 isthe 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).

Punjab Assignment Help

Buy Online Assignment Help services for COMPUTER ASSIGNMENT with Punjab Assignment Help at punjabassignmenthelp@gmail.com