Views
Write a script for creating a variety of database views to demonstrate your understanding of different view types and their use cases. Your script should include:
- Horizontal views (selecting specific columns, e.g., a view showing only book titles and authors)
- Vertical views (selecting specific rows based on conditions, e.g., a view showing only overdue borrowings)
- Mixed views (combining both column and row selection, e.g., a view showing active members and their contact info)
- Views that involve joining multiple tables (e.g., a view showing members and the books they have borrowed)
- Views that use subqueries (e.g., a view showing books with average rating above 4)
- Views that use
UNIONoperations (e.g., a view combining current and past reservations) - A view that selects from another view (e.g., a summary view based on a detailed view)
- A view with the
CHECK OPTIONto enforce updatable view constraints (e.g., a view allowing updates only for available books)
For each view, include comments explaining its purpose and how it relates to your overall database design. Ensure your scripts are well-structured and can be executed without errors in PostgreSQL.
Submit your views as a single SQL file unless otherwise instructed. Optionally, include sample SELECT statements to demonstrate the output of each view.
On top of the code and documentation deliverables, each team member must record a short video (approximately 2 minutes per person) describing the part of the solution they were responsible for. The video should briefly explain your contribution, the challenges you faced, and how you addressed them. The free version of Zoom allows recording of videos during a conference call, making it easy for teams to collaborate and record together. Once recorded, the video can be uploaded to Google Drive or YouTube (as an unlisted video), and the link should be provided as part of the graded material.