Project 1 SQLThe deadline for project 1 is:Fri 08 Nov, 5:00 pm1. AimsThis 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.? The goal is to build some useful data access operations on the MyMyUNSW database. Atheme of this project is dirty data. As I was building the database, using a collection ofreports from UNSWs information systems and the database for the academic proposalsystem (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 thestudent data, but had no enrolment records, and, worse, enrolment records with marks andgrades for students who did not exist in the student data). I removed most of theseproblems as I discovered them, but no doubt missed some. Some of the exercises below aimto uncover such anomalies; please explore the database and let me know if you find otheranomalies.2. How to do this project:? Read this specification carefully and completely? Familiarize yourself with the database schema (description, SQL schema, summary)? Make a private directory for this project, and put a copy of the proj1.sql template there? You must use the create statements in proj1.sql when defining your solutions? Look at the expected outputs in the expected_qX tables loaded as part of the check.sql file? Solve each of the problems below, and put your completed solutions into proj1.sql? Check that your solution is correct by verifying against the example outputs and by usingthe check_qX() functions? Test that your proj1.sql file will load without error into a database containing just theoriginal MyMyUNSW data? Double-check that your proj1.sql file loads in a single pass into a database containing justthe original MyMyUNSW data? Submit the project via give? PLpgSQL functions are not allowed to use in this project? For each question, you must output result within 120 seconds on Grieg server.3. IntroductionAll Universities require a significant information infrastructure in order to manage their affairs. Thistypically involves a large commercial DBMS installation. UNSWs student information system sitsbehind the MyUNSW web site. MyUNSW provides an interface to a PeopleSoft enterprisemanagement system with an underlying Oracle database. This back-end system (Peoplesoft/Oracle)is often called NSS.UNSW has spent a considerable amount of money ($80M+) on the MyUNSW/NSS system, and ithandles much of the educational administration plausibly well. Most people gripe about the quality ofthe MyUNSW interface, but the system does allow you to carry out most basic enrolment tasks online.Despite its successes, MyUNSW/NSS still has a number of deficiencies, including:? no waiting lists for course or class enrolment? no representation for degree program structures? poor integration with the UNSW Online HandbookThe first point is inconvenient, since it means that enrolment into a full course or class becomes asequence of trial-and-error attempts, hoping that somebody has dropped out just before you attemptto enrol and that no-one else has grabbed the available spot.The second point prevents MyUNSW/NSS from being used for three important operations that wouldbe extremely helpful to students in managing their enrolment:? finding out how far they have progressed through their degree program, and what remainsto be completed? checking what are their enrolment options for next semester (e.g. get a listof suggested courses)? determining when they have completed all of the requirements of their degree program andare eligible to graduateNSS contains data about student, courses, classes, pre-requisites, quotas, etc. but does not containany representation of UNSWs degree program structures. Without such information in the NSSdatabase, it is not possible to do any of the above three. So, in 2007 the COMP9311 class devised adata model that could represent program requirements and rules for UNSW degrees. This was builton top of an existing schema that represented all of the core NSS data (students, staff, courses, classes,etc.). The enhanced data model was named the MyMyUNSW schema.The MyMyUNSW database includes information that encompasses the functionality of NSS, the UNSWOnline Handbook, and the CATS (room allocation) database. The MyMyUNSW data model, schemaand database are described in a separate document.4. Setting UpTo install the MyMyUNSW database under your Grieg server, simply run the following twocommands:$ createdb proj1$ psql proj1 -f /home/cs9311/web/19T3/proj/proj1/mymyunsw.dumpIf youve already set up PLpgSQL in your template1 database, you will get one error message as thedatabase starts to load:psql:mymyunsw.dump:NN: ERROR: language plpgsql already existsYou can ignore this error message, but any other occurrence of ERROR during the load needs to beinvestigated.If everything proceeds correctly, the load output should look something like:SETSETSETSETSETpsql:mymyunsw.dump:NN: ERROR: language plpgsql already exists... if PLpgSQL is not already defined,... the above ERROR will be replaced by CREATE LANGUAGESETSETSETCREATE TABLECREATE TABLE... a whole bunch of theseCREATE TABLEALTER TABLEALTER TABLE... a whole bunch of theseALTER TABLEApart from possible messages relating to plpgsql, you should get no error messages. The databaseloading should take less than 60 seconds on Grieg, assuming that Grieg is not under heavy load. (If youleave your project until the last minute, loading the database on Grieg will be considerably slower,thus delaying your work even more. The solution: at least load the database Right Now, even if youdont start using it for a while.) (Note that the mymyunsw.dump file is 50MB in size; copying it underyour home directory or your /srvr directory is not a good idea).If you have other large databases under your PostgreSQL server on Grieg or you have large files underyour /srvr/YOU/ directory, it is possible that you will exhaust your Grieg disk quota. In particular, youwill not be able to store two copies of the MyMyUNSW database under your Grieg server. The solution:remove any existing databases before loading your MyMyUNSW database.If you are running PostgreSQL at home, you can download the files: mymyunsw.dump, proj1.sql to getyou started. You can grab the check.sql separately, once it becomes available.A useful thing to do initially is to get a feeling for what data is actually there. This may help youunderstand the schema better, and will make the descriptions of the exercises easier to understand.Look at the schema. Ask some queries. Do it now.Examples ...$ psql proj1... PostgreSQL welcome stuff ...proj1=# \d... look at the schema ...proj1=# select * from Students;... look at the Students table ...proj1=# select p.unswid,p.name from People p join Students s on (p.id=s.id);... look at the names and UNSW ids of all students ...proj1=# select p.unswid,p.name,s.phone from People p join Staff s on (p.id=s.id);... look at the names, staff ids, and phone #s of all staff ...proj1=# select count(*) from Course_Enrolments;... how many course enrolment records ...proj1=# select * from dbpop();... how many records in all tables ...proj1=# select * from transcript(3197893);... transcript for student with ID 3197893 ...proj1=# ... etc. etc. etc.proj1=# \qYou will find that some tables (e.g. Books, Requirements, etc.) are currently unpopulated; theircontents are not needed for this project. You will also find that there are a number of views andfunctions defined in the database (e.g. dbpop() and transcript() from above), which may or may notbe useful in this project.Summary on Getting StartedTo set up your database for this project, run the following commands in the order supplied:$ createdb proj1$ psql proj1 -f /home/cs9311/web/19T3/proj/proj1/mymyunsw.dump$ psql proj1... run some checks to make sure the database is ok$ mkdir Project1Directory... make a working directory for Project 1$ cp /home/cs9311/web/19T3/proj/proj1/proj1.sql Project1DirectoryThe only error messages produced by these commands should be those noted above. If you omit anyof the steps, then things will not work as planned.NotesRead these before you start on the exercises:? the marks reflect the relative difficulty/length of each question? use the supplied proj1.sql template file for your work? you may define as many additional functions and views as you need, provided that (a) thedefinitions in proj1.sql are preserved, (b) you follow the requirements in each question onwhat you are allowed to define? make sure that your queries would work on any instance of the MyMyUNSW schema;dont customize them to work just on this database; we may test them on a differentdatabase instance? do not assume that any query will return just a single resu< even if it phrased as most orbiggest, there may be two or more equally big instances in the database? when queries ask for peoples names, use the Person.name field; its there precisely toproduce displayable names? when queries ask for student ID, use the People.unswid field; the People.id field is aninternal 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 notmatter; it can always be adjusted using order by. In fact, our check.sql will order yourresults automatically for comparison.? the precise formatting of fields within a result tuple does matter; e.g. if you convert anumber to a string using to_char it may no longer match a numeric field containing the samevalue, even though the two fields may look similar? develop queries in stages; make sure that any sub-queries or sub-joins that youre usingactually work correctly before using them in the query for the final view/function? You can define either SQL views OR SQL functions to answer the following questions.? If you meet with error saying something like代做data衬衬、SQL編程語言調(diào)試买猖、代寫SQL設(shè)計、datab “cannot change name of view column”, you candrop the view you just created by using command “drop view VIEWNAME cascade;” thencreate your new view again.Each question is presented with a brief description of whats required. If you want the full details ofthe expected output, take a look at the expected_qX tables supplied in the checking script.5. TasksTo facilitate the semi-auto marking, please pack all your SQL solutions into view or function asdefined in each problem (see details from the solution template we provided).Q1 (4 marks)Define an SQL view Q1(unswid,longname) that gives the distinct room id and name of anyroom that is Air-conditioned (refers to the facilities.description). The view shouldreturn the following details about each room:? unswid should be taken from Rooms.unswid field.? longname should be taken from Rooms.longname field.Q2 (4 marks)Define a SQL view Q2(unswid,name) that displays unswid and name of all the distinctstaff who taught the student Hemma Margareta. The view should return the following detailsabout each staff:? unswid should be taken from People.unswid field.? name should be taken from People.name field.Q3 (4 marks)Define a SQL view Q3(unswid,name) that gives all the distinct international studentswho enrolled in COMP9311 and COMP9024 in the same semester and got HD(Course_enrolments.mark >= 85) in both courses. The view should return the followingdetails about each student:? unswid should be taken from People.unswid field.? name should be taken from People.name field.Q4 (4 marks)Define a SQL view Q4(num_student) that gives the number of distinct students who getmore HD (Course_enrolments.mark >= 85) than average student. For example, if on average astudent gets 3 HDs, we only count students who get more than 3 HDs.Note:? when calculating the average, only consider students who have at least one not null mark.Q5 (5 marks)Define a SQL view Q5(code,name,semester) that displays the course(s) with the lowestmaximum mark in each semester. The view should return the following details about each course:? code should be taken from Subjects.code field.? name should be taken from Subjects.name field.? semester should be taken from Semesters.name field.Note:? only consider valid courses which have at least 20 not null mark records.? if several courses have the same lowest maximum mark in one semester, return all of them.? skip the semester with no valid course.Q6 (5 marks)? Define SQL view Q6(num), which gives the number of distinct local studentsenrolling in 10S1 in Management stream but never enrolling in any course offered byFaculty of Engineering.Note:? the student IDs are the UNSW ids (i.e. student numbers) defined in the People.unswidfield.? Do not count duplicate records.Q7 (6 marks)Database Systems course admins would like to know the average mark of each semester. Define aSQL view Q7(year, term, average_mark) to help them monitor the average mark eachsemester. Each tuple in the view should contain the following:? the year (Semesters.year) of the semester? the term (Semesters.term)? the average mark of students enrolled in this course this semester as numeric(4,2)Database Systems has value ‘Database Systems’ in the Subjects.name field. You can find theinformation about all the course offerings for a given subject from Courses. You should calculatethe average mark of enrolled students for a course offering from the table Course_enrolments.Note:? There are two subjects that share the same name “Database Systems”, and we do notdistinguish them in this question. In consequence, you may find more than one course for asingle semester. In such case, there is no student enrolling in more than one course.? When calculating the average marks, only consider not null mark records.? Only consider the semesters which have ‘Database Systems’.Q8 (6 marks)The head of school would like to know the performance of students in a set of CSE subjects. Asubject in this set has two properties: (1) its subject code must start with “COMP93”, and (2) it mustbe offered in every major semester (i.e., S1 and S2) from 2004 (inclusive) to 2013 (inclusive). Thehead of school requests a list of students who failed every subject in the set. We say a student fails asubject if he/she had received a not null mark Define a SQL view Q8(zid, name) for the head of school. Each tuple in the view should containthe following:? zid (‘z’ concatenating with People.unswid field)? student name (taken from the People.name field)Note:? For a given subject, the number of course offerings that a student can enroll in is at least one.For example, one may fail the course offering of a subject in 03S1, and then re-enroll inanother course offering of the same subject in 04S1.? Assume there are two subjects in the set, A and B. We only count students who failed both Aand B, but not students who failed either A or B.Q9 (6 marks)Define SQL view Q9(unswid,name)that gives all the distinct students who are satisfied thefollowing conditions in one program:? enroll a program in BSc (refer to program_degrees.abbrev)? must pass at least one course in the program in semester 2010 S2.? average mark >= 80. Average mark means the average mark of all courses a student haspassed before 2011(exclusive) in the program.? the total UOC (refer to subjects.uoc) earned in the program should be no less than therequired UOC of the program (refer to programs.uoc). A student can only earn the UOCof the courses he/she passed.The view should return the following details about each student:? unswid should be taken from People.unswid field.? name should be taken from People.name field.Note:? to pass a course, a student must get at least 50 in that course(Course_enrolments.mark >= 50).? if a student has enrolled into several different programs, you need to calculate the UOC andaverage mark separately according to different programs. A course belongs to a program ifthis student enrolls into course and program in a same semester (refer to semesters.id).Q10 (6 marks)The university is interested in the Lecture Theatre usage status in 2011 S1. So please defineSQL view Q10(unswid,longname,num,rank), which gives the distinct room id andname of all the Lecture Theatre with the number of distinct classes that use this theatre andthe rank. Theatre rankings are ordered by num from highest to lowest. If there are multiple theatreswith the same num, they have the same rank. The view should return the following details about eachtheatre:? unswid should be taken from Rooms.unswid field.? longname should be taken from Rooms.longname field.? num counts the total number of distinct classes that use this theatre.? rank records the rank of the number of distinct classes that use this theatre.Note:? if there is no class using a theatre, the num would be 0.? the ranking is with gaps. i.e., if there are 2 theatres ranked as first, the third theatre will beranked as third.before 2011 (exclusive)6. SubmissionYou can submit this project by doing the following:? The file name should be proj1.sql.? Log into the CSE server, ensure that you are in the directory containing the file to besubmitted.? Type “give cs9311 proj1 proj1.sql” to submit.? You can also use the web give system to submit.? If you submit your project more than once, the last submission will replace the previous one? In case that the system is not working properly, you must take the following actions:? Please keep a screen capture (including timestamp and the size of the submitted file) foryour submissions as proof. If you are not sure how, please have a look at the guidelines.? Please keep a copy of your submitted file on the CSE server. If you are not sure how, pleasehave a look at taggi.The proj1.sql file should contain answers to all of the exercises for this project. It should be completelyself-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 schemafrom mymyunsw.dump)? the data in this database may be different from the database that youre using for testing? a new check.sql file may be loaded (with expected results appropriate for the database)? the contents of your proj1.sql file will be loaded? each checking function will be executed, and the results recordedBefore you submit your solution, you should check that it will load correctly for testing by usingsomething like the following operations:$ dropdb proj1 ... remove any existing DB$ createdb proj1 ... create an empty database$ psql proj1 -f /home/cs9311/web/19T3/proj/proj1/mymyunsw.dump ... load the MyMyUNSWschema and data$ psql proj1 -f /home/cs9311/web/19T3/proj/proj1/check.sql ... load the checking code$ psql proj1 -f proj1.sql ... load your solution$ psql proj1proj1=# select check_q1(); … check your solution to question1…proj1=# select check_q5(); … check your solution to question5…proj1=# select check_q10(); … check your solution to question10proj1=# select check_all(); … check all your solutionsNote: if your database contains any views or functions that are not available in a file somewhere, youshould put them into a file before you drop the database.If your code loads with errors, fix it and repeat the above until it does not.You must ensure that your proj1.sql file will load correctly (i.e. it has no syntax errors and it containsall of your view definitions in the correct order). If I need to manually fix problems withyour proj1.sql file in order to test it (e.g. change the order of some definitions), you will be fined via half of the mark penalty for each problem. In addition, make sure that your queries are reasonablyefficient. For each question, you must output result within 120 seconds on Grieg server. This timerestriction applies to the execution of the ‘select * from check_Qn()’ calls. For each question, youwill be fined via half of the mark penalty if your solution cannot output results within 120 seconds.7. Late Submission Penalty20% reduction for each day late.轉(zhuǎn)自：http://www.3daixie.com/contents/11/3444.html