User Account

All Pages

Write a PLpgSQL function that takes a staffs id

Assignment Help Database Management System
Reference no: EM131965083

PostgreSQL Database System Assignment - Project 2 PLpgSQL

Aims - This project aims to give you practice in

  • reading and understanding a moderately large relational schema (MyMyUNSW)
  • implementing SQL queries and views to satisfy requests for information
  • implementing PLpgSQL functions to aid in satisfying requests for information

The goal is to build some useful data access operations on the MyMyUNSW database. A theme of this project is "dirty data". As I was building the database, using a collection of reports from UNSW's information systems and the database for the academic proposal system (MAPPS), I discovered that there were some inconsistencies in parts of the data (e.g. duplicate entries in the table for UNSW buildings, or students who were mentioned in the student data, but had no enrolment records, and, worse, enrolment records with marks and grades for students who did not exist in the student data). I removed most of these problems as I discovered them, but no doubt missed some. Some of the exercises below aim to uncover such anomalies; please explore the database and let me know if you find other anomalies.

Tasks -

Q1 - You may use any combination of views, SQL functions and PLpgSQL functions in this question. However, you must define at least a PLpgSQL function called Q1.

Please write a PLpgSQL function Q1(course_id integer) that takes a course_id as parameter and outputs two numbers: (1) among all the rooms in UNSW, the number of rooms which can carry all the students enrolled in this course (i.e., rooms.capacity >= the total number of students enrolled in this course); (2) among all the rooms in UNSW, the number of rooms which can carry all the students enrolled in this course and also carry all the students in the enrolment waitlist of this course (i.e., rooms.capacity >= the total number of students enrolled in this course + the total number of students in the enrolment waitlist of this course).

Q2 - You may use any combination of views, SQL functions and PLpgSQL functions in this question. However, you must define at least a PLpgSQL function called Q2.

Please write a PLpgSQL function Q2(staff_id integer) that takes a staff's id as parameter and returns all teaching records of the given staff. Each transcript tuple should contain the following information: cid, term, code, name, uoc, avg_mark, highest_mark, median_mark, totalEnrols.

Q3 - You may use any combination of views, SQL functions and PLpgSQL functions in this question. However, you must define at least a PLpgSQL function called Q3.

Given the id of an organizational unit, please write a PLpgSQL function Q3 (org_id integer, num_courses integer, min_score integer) to help the UNSW administrative officers to find out all the students satisfying the following: (1) he/she has taken more than num_courses courses offered by the given organization (note that a student may fail a course and take it again, thus we need to count duplicate courses); (2) he/she has got score no less than min_score for at least one course offered by the given organization. Each tuple should include:

  • His/Her unswid (should be taken from People.unswid)
  • His/Her student_name (should be taken from People.name)
  • His/Her course_records

course_records of a student is a concatenation of several records with ','. Each record is about a course he/she has taken and is offered by the given organization. Each record should include the code of the course (Subjects.code), the name of the course (Subjects.name), the semester that he/she has learned this course (Semesters.name), the name of the organization that offers the course (OrgUnits.name), and the score he/she got (Course_enrolments.mark).

Attachment:- Assignment Files - Project 2 PLpgSQL.rar

Verified Expert

The Work consisted of analyzing the database schema of a large POSTGRE SQL database and writing PL/SQL functions to obtain 3 data analytics questions from the provided data set. The answers were cross-checked with the validation test scripts provided in the assignment and found to be obtained correctly.

Reference no: EM131965083

Questions Cloud

Find the worth of the put option : The current stock price of Johnson & Johnson is $74, and the stock does not pay dividends. The instantaneous risk-free rate of return is 4%.
Operate without traditional budgets : Do you think that an organization could operate without traditional budgets? If so, what might be used instead? And which references you based your answer on?
Substitute for bank borrowing in fueling growth : How do you see the role of corporate bonds as a substitute for bank borrowing in fueling growth? and what references you based your answer on?
Design an australian population calculator program : You will design and implement an Australian population (AP) calculator program for client that allows users to input state/territory name and the population.
Write a PLpgSQL function that takes a staffs id : PostgreSQL Database System Assignment - Project 2 PLpgSQL. Please write a PLpgSQL function Q2(staff_id integer) that takes a staff's id as parameter
How does a not-for-profit firm differ from all the other : In terms of Ownership/Governance, how does a Not-For-Profit firm differ from all the other firms we have discussed in LBS?
Substitute for bank borrowing in fueling growth : How do you see the role of corporate bonds as a substitute for bank borrowing in fueling growth? and what references you based your answer on?
What is the net present value of project : The tax rate is 35%. If the firm requires a 10% rate of return, what is the Net Present Value of this project?
Are there advantages that they might have that do not exist : Are there advantages that they might have that do not exist in the US? What other issues might a foreign company have to face with regard to infomation systems?

Reviews

len1965083

5/1/2018 5:38:49 AM

Subject: PostgreSQL Database System. Detailed Question: I need help with postgresql homework. Submission - You can submit this project by doing the following: The file name should be proj2.sql. Ensure that you are in the directory containing the file to be submitted. Type "give cs9311 proj2 proj2.sql" If you submit your project more than once, the last submission will replace the previous one. To prove successful submission, please take a screenshot as assignment submission manual shows and keep it by yourself. Each question is presented with a brief description of what's required. If you want the full details of the expected output, take a look at the expected_qX tables supplied in the checking script.

len1965083

5/1/2018 5:38:43 AM

If you have any problems in submissions, You can also ask questions about this project in our project online Q&A group, we will answer your questions as soon as possible. The proj2.sql file should contain answers to all of the exercises for this project. It should be completely self-contained and able to load in a single pass, so that it can be auto-tested as follows: a fresh copy of the MyMyUNSW database will be created (using the schema from mymyunsw.dump), the data in this database may be different from the database that you're using for testing, a new check.sql file will be loaded (with expected results appropriate for the database), the contents of your proj2.sql file will be loaded and each checking function will be executed and the results recorded.

len1965083

5/1/2018 5:38:36 AM

Notes - Read these before you start on the exercises: the marks reflect the relative difficulty/length of each question - use the supplied proj2.sql template file for your work, you may define as many additional functions and views as you need, provided that (a) the definitions in proj2.sql are preserved, (b) you follow the requirements in each question on what you are allowed to define, make sure that your queries would work on any instance of the MyMyUNSW schema; don't customize them to work just on this database; we may test them on a different database instance, do not assume that any query will return just a single result; even if it phrased as "most" or "biggest", there may be two or more equally "big" instances in the database, you are not allowed to use limit in answering any of the queries in this project.

len1965083

5/1/2018 5:38:31 AM

When queries ask for people's names, use the People.name field; it's there precisely to produce displayable names, when queries ask for student ID, use the People.unswid field; the People.id field is an internal numeric key and of no interest to anyone outside the database, unless specifically mentioned in the exercise, the order of tuples in the result does not matter; it can always be adjusted using order by. In fact, our check.sql will order your results automatically for comparison. The precise formatting of fields within a result tuple does matter; e.g. if you convert a number to a string using to_char it may no longer match a numeric field containing the same value, even though the two fields may look similar, develop queries in stages; make sure that any sub-queries or sub-joins that you're using actually work correctly before using them in the query for the final view/function.

Write a Review

Database Management System Questions & Answers

  Write a program stored in a file named rain

Write a program, stored in a file named rain.py, that performs the following task. If there is no file with that name in the working directory, then the program outputs an (arbitrary) error message and exits.

  Provide a summary of normalizing database tables

Suppose that you are the database developer for a local college. The Chief Information Officer (CIO) has asked you to provide a summary of normalizing database tables that the IT staff will use in the upcoming training session. Describe the steps ..

  Return the empid firstname lastname for the employee

Return the empid, firstname, lastname, and city for the employee with empid 9. Return a list of employee id's, last names, and first names for employees hired on or after January 1, 2004. Retrieve the product name and unit price of all products that ..

  Normalization procedures suggested by shome and cornell

These are normalization procedures suggested by Shome and Cornell. EQ Tools program permits any of these normalization (or scaling) approaches.

  Importance have button that takes you back to main page

What is the importance to have a button that takes you back to the main page on each web page? How does dead end or orphan pages affect the continuity of a website?

  Create the star schema for the data warehouse

Develop a specific and detailed discussion about the ETL process is to be used to move data from the OLTP environment to the data warehouse - Create a star schema and the DDL to create the Star schema for the data warehouse.

  Explain what do business rules require to be effective

What are the advantages of having the DBMS between the end user's applications and the database. What are some reasons for studying file systems. IT 645 - Database Management Systems

  Write sql statements to perform the given operations

Write SQL statements to perform the following operations on the wine cellar database.

  Database and programming design

Database and Programming Design

  The strategic planning for database systems

Write clearly and concisely about topics related to the strategic planning for database systems using proper writing mechanics and technical style conventions.

  Write-up thoughts on the tutorial

Write-up thoughts on the tutorial on given tutorials: 2005 Tutorials

  How to perform document classification using tools in weka

How to perform document classification using tools in WEKA

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

Submit Assignment

Academics

Major Subjects

Majors

Get In Touch

whatsapp: +1-415-670-9521

Phone: +1-415-670-9521

Email: [email protected]

TERMS & POLICIES

HELP & SUPPORT

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd