Reference no: EM133777110

Database Fundamentals

Instructions:

Group Roles and Tasks:

Each group member is responsible for a specific aspect of the project, with all tasks focused on designing and implementing a functional relational database application using SQL.

Member 1: Data Requirements Analysis and Conceptual Design

Task 1: Identify data requirements and develop an Entity-Relationship Diagram (ERD)
Analyse the case study to identify the data requirements for managing census information.
Develop an ERD that captures the relationships between citizens, households, and census reports.
Ensure the ERD includes entities, attributes, primary keys, foreign keys, and correct cardinality (one-to-one, one-to-many relationships).
Deliverables:
A written description of data requirements based on the census system.
A completed ERD showing the relationships between citizens, households, and census reports.

Member 2: Relational Database Schema Design and SQL DDL Implementation

Task 2: Design the relational schema and implement the database using Data Definition Language (DDL)
Convert the ERD into a relational schema, including tables for citizens, households, and census reports.
Write SQL queries using DDL to create the tables with appropriate constraints (primary keys, foreign keys, not null, unique).
Ensure all relationships are implemented properly through foreign key constraints.

Deliverables:
A SQL script with DDL statements to create the database schema. Screenshots showing successful table creation in the chosen RDBMS.

Member 3: Data Control Language (DCL) and Data Manipulation Language (DIME} Developer

Task 3: Implement Data Control Language (DCL) and Data Manipulation Language (DML)

Learning Outcome 1: Explain the fundamentals of database languages, models and architecture
Learning Outcome 2: Identify data requirements and apply relational modelling concepts and principles to develop Entity Relationship Diagram.
Learning Outcome 3: Investigate problem and apply normalization techniques to design a database schema.
Learning Outcome 4: Critically analyse, identify data requirements, design, and implement a relational database application by applying relational database management systems concepts like data modelling concept, data definition language, data control language, data manipulation language.

Overview:
To design and implement a database system for managing national census data. The system will store, and process information related to citizens, households, and census reports. The assignment emphasizes database design, data management, and query implementation using relational database management system (RDBMS) concepts.

Case Study: The government is conducting a national census to gather vital demographic data on citizens and households across the country. Your group is tasked with developing a relational database application to manage the following information:
• Citizens: Citizen ID, Name, Gender, Date of Birth, Address, Contact Information, Employment Status, Nationality.
• Households: Household ID, Address, Number of Members, Head of Household,
• Census Reports: Report ID, Census Year, Total Population, Number of Households, and other statistical data.
• Household-Citizen Relationship: Each household is associated with one or more citizens, and each citizen belongs to exactly one household.

Your goal is to create a database that allows the efficient storage, retrieval, and analysis of census data. The database should support queries that provide information such as the total population of a specific city, the average household size, or the number of citizens in a certain age group.

Write SQL queries using DCL to manage user permissions (e.g., admin role for database creation, read-only access for data analysts).

Write SQL queries using DML to insert sample data into the tables (e.g., citizens, households, census reports).
Control user access using DCL to restrict or grant privileges to different roles.

Deliverables:
• A SQL script containing DCL queries for managing user roles and permissions. A SQL script containing DML queries for inserting sample data into the database.
Screenshots of successful data insertion and user access control.

Member 4: Query Development and Data Retrieval

Task 4: Develop and test SQL queries for data retrieval

Write SQL queries to retrieve and analyse census data. Some example queries include:
• Calculate the total population of a specific city.
- Find the average household size in a specific region.
- Retrieve the number of citizens in a certain age group or with a specific employment status.
• Generate a report on the census data for a specific year.
Ensure the queries are efficient and use appropriate JOIN operations to retrieve data from multiple tables.