|
|
Working together or copying the ideas of others
You are expected to produce your own solutions to this assignment. Copying other peoples’ work is considered plagiarism and is not acceptable. However, I understand that it is useful to talk over assignments like this with others in the initial stages in order to get an idea of the scope of the problem, or understand basic techniques. Ask me if you are not sure about the rules.
Warning: the CIS department reports all cases of plagiarism and cheating to the Director of Student Services. Minimum penalty for a first offence is a zero for the assignment or exam, and the offense will be on your student record for two years. Subsequent offences bring higher penalties.
How to submit your assignment
Through the use of GitHub to your working folder to clone and download to your computer, copy your work file into your "repository" inside, and then uploaded to the GitHub class organization.More details...
▼Assignment1
Due:
Instructor: Huo Huan
- (5 marks) Exercise 4.8 from the text, p. 108.
- (5 marks) Exercise 5.12, relational algebra only, for parts c. and e. only, from the text, p. 130.
For the following PostgreSQL questions, note that a complete description of all the tables in the sample database is given at http://www.postgresqltutorial.com/postgresql-sample-database/.
Submit screen shots of your successful PostgreSQL queries showing both the query and the result.
Use ALT-PrintScrn to copy just the active window to the clipboard, then paste it into a Word document.
- (5 marks) Construct a SQL query on the dvdrental database that uses a where clause on one table. (E.g. find customers at a particular store.)
- (5 marks) Construct a SQL query on the dvdrental database that uses a natural join of two or more tables and an additional where condition. (E.g. find the titles of films rented by a particular customer.)
- (5 marks) Construct a SQL query on the dvdrental database that uses an aggregate with group by and having clauses. (E.g. show the title and number of rentals of all films having more than one rental.)
You’ll want to look at the data in the tables first to see what are reasonable queries.
I will post hints for using PostgreSQL and some of these questions – keep watching the course news page.
▼Assignment2
Due:
Instructor: Huo Huan
- (10 marks) For the postgreSQL dvdrental database, construct a view on two or more tables including an aggregate function. Test it with a query to show that it works, and hand in a screen shot of the query in the SQL window and the results.
- (20 marks) Using the postgreSQL DBMS, create a new database called enrolments.
- Create a simple ER (or UML) diagram for students and courses.
- Indicate the required and optional relationships and multiplicity.
- Create three tables for students, course sections and enrolments. Just create a few columns for each relation, enough to make the joins and do some simple queries.
- Specify the primary keys and referential integrity where necessary.
- Implement the mandatory relationships with NOT NULLs.
- Input some sample data for three students and a few courses they are taking.
- Construct a query using a join on all three tables and show the result.
Hand in your results on paper, using ALT-PrntScrn to show the windows.
I'll post hints and perhaps cover some of this in class; keep looking at the course news page.
▼Assignment3
Due:
Instructor: Huo Huan
- (10 marks) More normalization:
- Explain the highest normal form that the following relation MUSICIAN is in, and give reasons for your answer (no reasons, no marks).
MUSICIAN(MusicianID, Instrument, Name, Band, Bandleader, Tutor)
A musician may play many instruments (so the key is MusicianID, Instrument).
- MusicianID,Instrument → Name, Band, Bandleader, Tutor
- MusicianID → Name, Band, Bandleader
- Band → Bandleader
- Describe an update anomaly that could occur with bands and bandleaders.
- Decompose MUSICIAN into the best normal form you can – explain each step of your work and why the final relations are in a better normal form.
- (10 marks) Exercise 15.9 from the text, p. 411.
(10 marks) Exercise 16.16 from page 437 of the text, using case study B.1 in Appendix B.
Watch for hints and help on the course news page.
|
|