Objectives

Use the database planning process introduced in this week’s lecture to begin planning and design for your database.

Description

Database Planning Assignment and ER Diagrams assignment will help you on the second major project. This         assignment will focus on the necessary preparations that should be done before building the database, and help you figure out your database details.

Please follow the steps below to figure out your database details. There are two parts to this assignment:

Part 1: Define your database

Part 2: Find the information required

Part 3: Draw an ER diagram for a specific scenario

Part 4: An ER diagram for your database

Note: Please don’t use database examples in this assignment or on the Canvas lectures.

What to Hand In

Post this document, with all the items in red filled out, to Canvas on the “A8: Database Planning” assignment page.

Part 1: Define your database

First, you need to define your database. It is your basic idea of your database and why you want to build your database.

1.    Describe the purpose of your database. For example, it can be a database for an airline reservation        system or an online shopping website. You can get your idea from a business problem or a hobby you are interested in.

[My Database is for a fitness tracking application that helps users track their workout routines and diet plans. The goal of this database is to provide a platform for users to record and monitor their fitness    progress as well as access personalized workout and diet plans to achieve their fitness goals.]

2.    Describe who will use the database. You need to come up with at least two different groups of people

(e.g., customers and administrators for the airline reservation system) and separately describe their purpose of using your database.

[Users: Individuals who want to track their fitness progress and get a personalized workout and diet plan. Trainers: Personal trainers who use the app to create and manage workout and diet plans for their clients. ]

Part 2: Find the information required.

After you get your idea of your database, you need to collect information for that. Here we help you find what to collect and where to collect

1.    Specify the data you want (what to collect). According to your idea of your database in Part 1, list at least

10 types of data that you think needs to be collected for your database and add a short description for each type of data. For example, the first name and last name of employees may be needed for a company’s        employee database. Your final database will have many more data points.

[User information: name, age, gender, email, password, height, weight, body fat percentage.    Workout log: exercise type, duration, date, intensity level, calories burned, distance, sets, reps.

Diet log: food type, serving size, calories consumed, macronutrient breakdown (protein, carbs, fat), meal time.

Personalized workout plans: exercise type, duration, frequency, intensity level, goal (strength, endurance, weight loss, muscle gain).

Personalized diet plans: daily calorie goal, macronutrient breakdown, food recommendations, meal timing. Fitness goals: short-term and long-term fitness goals of the user, such as running a 5K, losing 10 pounds, or increasing strength in specific muscle groups.

Progress tracking: measurements of body metrics, such as waist circumference, body mass index (BMI), and muscle mass, to track progress over time.

Heart rate monitoring: tracking of heart rate during exercise to monitor intensity and provide feedback on performance.

Exercise equipment: the type of equipment used for each exercise better to understand the user's workout preferences and limitations.

Nutritional supplements: tracking of the user's vitamins, minerals, and supplements to ensure proper nutrient intake and identify any potential interactions with the personalized diet plan.

]

Part 3: ER diagram example

Select one of the following scenarios for your ER diagram.

Scenario 1. Draw a single ER diagram that represents the specifications listed below. To help you do this, we break down the drawing for you into separate steps.

●    A company database needs to store information about employees (identified by SSN, with salary and phone as attributes), departments (identified by DNO, with dname and budget as attributes), and children of           employees (with name and age as attributes). Draw an ER diagram to represent these entities and their         attributes.

o  Extend your ER diagram with relationships (e.g., one-to-many) to represent the following information.

▪     Employees work in departments.

▪     Each department is managed by an employee (exactly one).

▪     A child must be identified uniquely by name when the parent (who is an employee) is known. We are not interested in information about a child once the parent leaves the

company.

Scenario 2. Draw a single ER diagram that represents the specifications listed below. To help you do this, we break down the drawing for you into separate steps.

●    Consider a model of an airport with planes, models of planes, test of planes, and technicians. Draw an ER diagram to represent these entities and the following attributes.

●    Planes have a unique registration number. Airplane Models are each identified by a model number (e.g.     DC-80), and each have a capacity and a weight. A number of technicians work at the airport. You need to store for each the name, phone number, address, and salary. The airport has a number of tests that are used regularly to ensure that airplanes are safe. Each test has a unique FAA number, a name, and a maximum    possible score. Extend your ER diagram with relationships to represent the following information.

o  Each airplane is of one specific model.

o  Each technician is an expert on one or more plane models. His or her expertise may overlap with that of other technicians.

o  Transport Canada requires the airport to keep track of each time a given airplane is tested using a given test. For each testing event, the information needed is the date, the number of hours spent   doing the test, and the score that the airplane received on the test.

After you have selected the scenario, please follow the steps below:

●    Identify the entities in the scenario. An entity is an object or concept that can be uniquely identified.

[employee, department, child]

Note: Example entities in a library management system may include books, authors, patrons, book_out, and employees.

●    Identify at least three relationships between the entities and apply appropriate cardinality constraints to  the relationships. A relationship is a connection between two or more entities. Cardinality constraints         specify the number of entities associated with one another in a relationship. For example, in a library           management system, one author can write multiple books, but only one can have one author. This would be represented as a one-to-many relationship with a cardinality constraint of 1:N.

Note: It may be helpful tofirst think of “rules” that guide the business. For example, in a library management system, there may be a relationship between a book (an entity) and book_out (an entity). The rule is - “the      library keeps a list of all book (books) checked out (book_out) by patrons (patrons) AND a book can only be   checkout out by one patron at a time” . This rule guides how relationships in our database are structured.

In the table below, add at least three relationships for the database scenario (hint: three are defined in the scenario).

●    Create the ER diagram. Include entity names, relationship names, and cardinality constraints in the diagram and add a screenshot of the ER diagram.  [

]