Starting from:
$30
CMPUT291-Assignment 1 Database Design using ER model and Mapping ER Model to Relational Model Solved
The goal of this assignment is to reinforce the concepts of database design using Entity-Relationship (ER) model and mapping an ER model into a relational model. This assignment has two parts.
Part I - Modeling
You are building a database for Service Alberta to maintain data for various services offered. Given the database specification below, your job is to turn the specification into an ER diagram. You are using dia (see documentation for dia) to draw your ER diagram. Your notation must be consistent with the notation used in our lecture notes. You can use all constructs and notations discussed in our lecture notes and nothing else (i.e., even notations used in the textbook but not in our lectures cannot be used).
You will be working in groups of 2-3; group members must be all registered in the course but they may not be all in the same lecture or lab section. Your ER diagram should capture all the information and constraints in the specification, but at the same time be minimal, meaning redundant entities, relationships, attributes and constraints should be avoided.
Database Specification
The database keeps detailed information about vehicles, drivers, vital records, etc.
Each person (known to Service Alberta) has a first name, a last name, a birth date, a birth place, an address and a phone number. You can assume a subset of the attributes (e.g., first name, last name and birth date) is unique. For each driver, eyes color and hair color are also recorded. Each traffic officer also has a city where the officer operates.
Records of births and marriages in the province are maintained. For each birth in Alberta, in addition to the personal details of the person born (as listed above), there is a unique registration number, a registration date, a registration place, a gender at birth, a mother and a father. A birth may be registered without a father but always has a mother. Each marriage in the province is also recorded, and it has a unique registration number, a registration date, a registration place, and the details of the partner persons, referred to as Partner 1 and Partner 2.
Drivers can obtain drivers' licenses. Each driver's license has a unique license number, a date issued, an expiry date and a license class. Each driver's license must be issued to a driver. There are a set of license classes, and each class has a unique id and a description.
Each vehicle has a unique VIN, a make, a model, a year, and a color. Vehicles are registered before they can be operated. Each vehicle registration in Alberta has a unique registration number, a registration date, an expiry date, an Alberta plate and a driver registered as the owner. A vehicle can have multiple registrations and a driver can also have multiple registrations.
Registered vehicles can be given tickets. Each ticket given to a registered vehicle has a unique ticket number, an offence date, the offence cited, a fine dollar amount, and sometimes a traffic officer issuing the ticket. Drivers can have demerit point notices in their records. Each notice has a date, a description and the number of demerit points.
Part II - Mapping
Map the following ER diagram into relational tables using the rules discussed in class. Give the complete CREATE TABLE commands for each necessary table including attribute names, their domains/types and all possible constraints. Use your common sense to choose a domain for each attribute.
Part I - Modeling
You are building a database for Service Alberta to maintain data for various services offered. Given the database specification below, your job is to turn the specification into an ER diagram. You are using dia (see documentation for dia) to draw your ER diagram. Your notation must be consistent with the notation used in our lecture notes. You can use all constructs and notations discussed in our lecture notes and nothing else (i.e., even notations used in the textbook but not in our lectures cannot be used).
You will be working in groups of 2-3; group members must be all registered in the course but they may not be all in the same lecture or lab section. Your ER diagram should capture all the information and constraints in the specification, but at the same time be minimal, meaning redundant entities, relationships, attributes and constraints should be avoided.
Database Specification
The database keeps detailed information about vehicles, drivers, vital records, etc.
Each person (known to Service Alberta) has a first name, a last name, a birth date, a birth place, an address and a phone number. You can assume a subset of the attributes (e.g., first name, last name and birth date) is unique. For each driver, eyes color and hair color are also recorded. Each traffic officer also has a city where the officer operates.
Records of births and marriages in the province are maintained. For each birth in Alberta, in addition to the personal details of the person born (as listed above), there is a unique registration number, a registration date, a registration place, a gender at birth, a mother and a father. A birth may be registered without a father but always has a mother. Each marriage in the province is also recorded, and it has a unique registration number, a registration date, a registration place, and the details of the partner persons, referred to as Partner 1 and Partner 2.
Drivers can obtain drivers' licenses. Each driver's license has a unique license number, a date issued, an expiry date and a license class. Each driver's license must be issued to a driver. There are a set of license classes, and each class has a unique id and a description.
Each vehicle has a unique VIN, a make, a model, a year, and a color. Vehicles are registered before they can be operated. Each vehicle registration in Alberta has a unique registration number, a registration date, an expiry date, an Alberta plate and a driver registered as the owner. A vehicle can have multiple registrations and a driver can also have multiple registrations.
Registered vehicles can be given tickets. Each ticket given to a registered vehicle has a unique ticket number, an offence date, the offence cited, a fine dollar amount, and sometimes a traffic officer issuing the ticket. Drivers can have demerit point notices in their records. Each notice has a date, a description and the number of demerit points.
Part II - Mapping
Map the following ER diagram into relational tables using the rules discussed in class. Give the complete CREATE TABLE commands for each necessary table including attribute names, their domains/types and all possible constraints. Use your common sense to choose a domain for each attribute.
1 file (498.2KB)
