Project 1 SQLSQL代写 This project aims to give you practice inreading and understanding a moderately large relational schema (MyMyUNSW).The deadline for project 1 is:Mon 22 Apr, 10:00 am1. Aims SQL代写This project aims to give you practice inreading and understanding a moderately large relational schema(MyMyUNSW).implementing SQL queries and views to satisfy requests forThe 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.2. How to do this project:Read this specification carefully andcompletelyFamiliarize yourself with the database schema (description, SQL schema,summary)Make a private directory for this project, and put a copy of the sql templatethereYou must use the create statements in sql when defining yoursolutionsLook at the expected outputs in the expected_qX tables loaded as part of the sqlfileSolve each of the problems below, and put your completed solutions intosqlCheck that your solution is correct by verifying against the example outputs and by using the check_qX() functions SQL代写Test that your sql file will load without error into a database containing just the original MyMyUNSWdataDouble-check that your sql file loads in a single pass into a database containing just the original MyMyUNSWdataSubmit the project viagivePLpgSQL functions are not allowed to use in thisprojectFor each question, you must output result within 120 seconds on Grieg3. IntroductionAll Universities require a significant information infrastructure in order to manage their affairs. This typically involves a large commercial DBMS installation. UNSW s student information system sits behind the MyUNSW web site. MyUNSW provides an interface to a PeopleSoft enterprise management 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 it handles much of the educational administration plausibly well. Most people gripe about the quality of the MyUNSW interface, but the system does allow you to carry out most basic enrolment tasks online.SQL代写Despite its successes, MyUNSW/NSS still has a number of deficiencies, including:no waiting lists for course or classenrolmentno representation for degree programstructurespoor integration with the UNSW OnlineHandbookThe first point is inconvenient, since it means that enrolment into a full course or class becomes a sequence of trial-and-error attempts, hoping that somebody has dropped out just before you attempt to 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 would be extremely helpful to students in managing their enrolment:finding out how far they have progressed through their degree program, and what remains to becompletedchecking what are their enrolment options for next semester (e.g. get a list of suggestedcourses)determining when they have completed all of the requirements of their degree program and are eligible tograduate SQL代写NSS contains data about student, courses, classes, pre-requisites, quotas, etc. but does not contain any representation of UNSW s degree program structures. Without such information in the NSS database, it is not possible to do any of the above three. So, in 2007 the COMP9311 class devised a data model that could represent program requirements and rules for UNSW degrees. This was built on 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 UNSW Online Handbook, and the CATS (room allocation) database. The MyMyUNSW data model, schema and database are described in a separate document.SQL代写4. Setting Up SQL代写To install the MyMyUNSW database under your Grieg server, simply run the following two commands:$ createdb proj1$ psql proj1 -f /home/cs9311/web/19s1/proj/proj1/mymyunsw.dumpIf you ve already set up PLpgSQL in your template1 database, you will get one error message as the database starts to load:psql:mymyunsw.dump:NN: ERROR: language plpgsql already existsSQL代写You can ignore this error message, but any other occurrence of ERROR during the load needs to be investigated.If everything proceeds correctly, the load output should look something like: SETSETSET SET SETpsql:mymyunsw.dump:NN: ERROR: language plpgsql already exists if PLpgSQL is not already defined, the above ERROR will be replaced by CREATE LANGUAGE SETSET SETCREATE TABLE CREATE TABLE a whole bunch of these CREATE TABLEALTER TABLE ALTER TABLE a whole bunch of these ALTER TABLEApart from possible messages relating to plpgsql, you should get no error messages. The database loading should take less than 60 seconds on Grieg, assuming that Grieg is not under heavy load. (If you leave 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 you don t start using it for a while.) (Note that the mymyunsw.dump file is 50MB in size; copying it under your home directory or your /srvr directory is not a good idea).SQL代写If you have other large databases under your PostgreSQL server on Grieg or you have large files under your /srvr/YOU/ directory, it is possible that you will exhaust your Grieg disk quota. In particular, you will 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 get you 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 you understand 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 SQL代写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=# q SQL代写You will find that some tables (e.g. Books, Requirements, etc.) are currently unpopulated; their contents are not needed for this project. You will also find that there are a number of views and functions defined in the database (e.g. dbpop() and transcript() from above), which may or may not be 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/19s1/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/19s1/proj/proj1/proj1.sql Project1DirectoryThe only error messages produced by these commands should be those noted above. If you omit any of the steps, then things will not work as planned.Notes SQL代写Read these before you start on the exercises:the marks reflect the relative difficulty/length of eachquestionuse the supplied sql template file for yourworkyou may define as many additional functions and views as you need, provided that (a) the definitions in proj1.sql are preserved, (b) you follow the requirements in each question on what you are allowed to definemake 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 instancedo 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 thedatabasewhen queries ask for people s names, use the namefield; it s there precisely to produce displayable nameswhenqueries ask for student ID, use the unswid field; the People.id field is an internal numeric key and of no interest to anyone outside the databaseunless 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;SQL代写

所有的编程代写范围：essayghost为美国、加拿大、英国、澳洲的留学生提供C语言代写、代写C语言、C语言代做、代做C语言、数据库代写、代写数据库、数据库代做、代做数据库、Web作业代写、代写Web作业、Web作业代做、代做Web作业、Java代写、代写Java、Java代做、代做Java、Python代写、代写Python、Python代做、代做Python、C/C++代写、代写C/C++、C/C++代做、代做C/C++、数据结构代写、代写数据结构、数据结构代做、代做数据结构等留学生编程作业代写服务。