Homework
Homework submission will be in Moodle environment
HW2 (due 26.03)
Practical Application of Database Design Principles
Objective: Design, implement, and query a straightforward database system.
Task 1: Reading Assignment
Instructions: Explore the online book titled "Database Design". Specifically, focus on Chapters 3 & 13, which discuss the "Database Development Process". After reading, fill out the "Database Development & Characteristics Concept Test" in moodle.
Task 2: Design a Database Schema
Scenario: Your task is to create a database design for a small-scale library system. This system requires tracking information related to books, authors, library patrons, and book loans. Requirements:
- Construct an Entity-Relationship (ER) diagram to represent the library system. Your diagram should include entities for books, authors, patrons, and loans.
- Keep in mind possible many-to-many relationships.
- You can add more tables to complete your design.
- Compose a concise description of your design, detailing the relationships between the entities.
Task 3: Implement the Database
Tools: Choose a relational database management system (RDBMS) you're comfortable with (options include SQLite, MySQL, PostgreSQL, DuckDB).
Steps:
- Construct the database and its tables in line with your design from Task 2.
- Populate each table with sample data, ensuring at least 5 entries per main entity table. (Feel free to use ChatGPT or Gemini for schema-based random data generation)
- Include SQL scripts or a link to an SQL file that details your schema creation and data insertion operations.
Task 4: Querying the Database
Objective: Develop SQL queries to execute the following tasks:
- Produce a list of all books authored by a specified individual.
- Identify all patrons who have overdue book loans.
- Enumerate books that have not ever been borrowed.
- Submission Requirements: Furnish the SQL queries alongside a brief explanation of the expected output for each query.
Deliverables: Your homework submission should consist of your ER diagram (pdf or png), SQL scripts for database creation and data entry, your SQL queries with expected outputs, and any accompanying explanations.
HW1 (due 12.03)
Data Source Exploration for Group Project
In this homework assignment, you will find a publicly available and reliable data source for a group project. After selecting a suitable data source, provide a concise overview of its key attributes, including:
Dataset name and a brief description
- Purpose of the data and its potential use in a group project
- Type of data (e.g., tabular, time series, geospatial) and data types (e.g., numerical, categorical, text)
- Update frequency and historical data availability
- Data ownership, licensing, and attribution requirements
- Privacy, ethical concerns, and necessary steps to address them
- Accessibility (e.g., direct download, API) and any API usage information
- Data size, scalability, and quality considerations
- Preprocessing and cleaning tasks are required before analysis
Please do not upload the data itself. The preferred submission format is markdown. You can write your homework at https://hackmd.io/ (requires login).