Relationships & Notations
One to One
One to Many
Many to Many
Zero/One to One
Zero/One to Many
Modeling Notation – Crow’s Foot
As an organization, we need to track our members and the committees they’re on within our organization. Each of our members can belong to any number of committees and that each committee can have any number of members. Also members are always assigned a single role on each committee (e.g Social Chair, Social General). Assume that:
Each individual can only serve one role within a committee and…
That each committee has its own set of specific roles that members can fulfill.
Create a database design for this described situation. The recommended steps would be to:
Identify data elements & subdivide into useful form. Feel free to make assumptions
Identify the entities (tables) and attributes (columns) for each
Can we identify any entity-to-entity relationships yet?
What are the primary keys, foreign keys, and remaining relationships?
NOTE: This assumes that a committee can have roles setup before we have people join the committee and they are assigned roles.
Problem Statement
Design an ERD based on business specifications. You need to see example above and need to design same ER model using boxes and arrows and form relationship between entities.
Use same arrows for relationship Crow’s foot. You can use https://app.diagrams.net/ for designing ER diagram.
Objective: Below is an overview of a Rideshare system. Please review the processes below to gain an understanding of the organizational context for which we are designing this database model.
Background: In the small town of Bend, Oregon, rideshare apps like Lyft and Uber were banned. We don’t have the background on why exactly but their loss is our potential gain! We’re part of a team that has been tasked with designing a prototype of a basic rideshare system for the town of Bend. You are on the database team and will be designing the initial database that will feed a mobile application being built by another team of front-end developers. The current nickname we’re giving is the app Ride-O .The goal for Ride-O 1.0 will be that it can support the basic functions of a rideshare app such as: Allowing people to sign-up as drivers, allowing people to sign-up as riders, connecting drivers and riders through rides, managing discounts and forms of payment for riders, and managing car details and payouts to drivers.
Driver sign-up process: When someone wants to sign up as a driver, they have to go to Bend City Hall where an employee of the Transportation & Parking office will help them set up their account. A new account requires the first and last name on their driver's license, drivers’ license number, email, home mailing address, and a single primary phone number. Also to have a new account set up, the details about their main car are required. Note, a driver could have many cars but to sign up only the info about one will be needed. To register a car we need the Make, Model, Year, Plate Number, Color, VIN of the vehicle, Insurance Company, Insurance Policy Number, and proof of a valid inspection. Lastly, to be officially set up as a driver, drivers need to have a bank account and provide the bank name, routing number, and account number of their account. We don’t need to track the account type. Note, even though this app is only for Bend, don’t assume all drivers (or riders) have a Bend address. Some will live in neighboring towns and have different zip codes.
Rider sign-up process: Signing up to be a rider is easy. A rider will only need to supply the first and last name, email, and phone number. They also need to provide one valid (i.e. unexpired) form of payment via credit or debit card.
Ride process: When a rider or group of people wants a rider, one rider with a valid Ride-O account can request a ride somewhere they will make the request for the app and be matched to a driver.Before the ride can be requested, a current address will either be entered or supplied based on the phone’s geolocation as well as a destination address.We’ll also want to track the date/time the ride was requested, the moment (i.e. date and time) it starts based on when the driver starts the ride, and the moment the driver says it ended. Each ride will also have the option of a rating of 1 to 5 that will be supplied by the rider after the ride ends. A rider should be able to take many rides in a day but obviously never two at the same time.It’s also possible they can get the same driver multiple times since Bend is a small town. Since this is just the initial version we’re not required to add fancy features like splitting the fair between people and food delivery.We’re also not going to do a shared ride feature.If 1 or more people want to share a ride, only one person will request and pay for the ride in the app.
Technical Details: NOTE: Each of these could be separate entities.
Riders – Ride-O will need to track a rider’s name, email, and phone. An option to store their address, city, and zip will be nice in case we want to store their home address to make it easy to hail rides home. There will not be a need to store the state since no one will be using this app outside of Oregon. Each rider should have at least one primary credit card even though that can have multiple cards on file. A primary_card_flag should be stored on the payment of (Y or N) so we know which card is their primary payment. To process a credit card, you must have the person’s full name, including middle if they have one. You then provide the card type which is a 4-character code like “VISA”, “MSTR” for MasterCard, or “AMEX” for American Express. You also capture the card number which is 15-16 digits, the expiration date, the 3-digit code on the back called the CC_ID, and the full billing address attached to that card since it doesn’t always have to match the mailing address attached to the customer’s account.
Drivers – We want all the key info on their drivers license like full name, address, city, state, zip, driver’s license number, and date of birth. We also want just a single email address and phone. A driver has to have one vehicle but can have many. We will track each vehicle’s year, make, model, color, VIN, plate number as well as the insurance company, policy number, and the date the inspection expires. If you want to include an inspection_expired_flag that stored a Y or N, that’s fine but since we can calculate expiration from the expiration_date, it’s really not needed. It’s possible two different drivers could share a car in the case they are cohabiting (e.g. roommates, spouses) and both are approved Ride-O drivers. Each driver should have their own bank account for payouts for their work. We only want to keep track of one bank account and for security reasons will store this info separately from the driver info. Bank account info will include a routing number and account number. Note, that when a driver who has multiple vehicles is driving, they have to select their “active vehicle” on their profile. This will help us know what vehicle they’re using when a ride is created.
Rides – When a ride is requested we capture the request_datetime which is fine to store as a date since the Oracle DATE datatype can also a date and time together. A ride will pair a valid driver to a valid rider and also identify what vehicle that driver is driving. We need this because we’ll need to signal the rider what vehicle to look for when it’s arriving. We also need to store the dropoff_address from the rider and also the start and end datetimes provided by the driver. After the ride ends we’ll give the rider the option to provide a rating of 1 to 5 for that ride. Lastly, riders have the chance to earn multiple one-time discount codes (e.g. 10% off) or free rides (e.g. stored as a 100% off discount) that do expire typically within 30 days from earning it as a standard rule. These discounts will automatically be applied after a ride. When the ride is applied it will be marked with a Used_flag of “Y”. While a rider can earn many discounts, a discount is specific to a rider and only applied to a single rider. We’ll want to store the discount type (e.g. % off, free ride), the % off, if it’s used or not, and the code’s expiration date.
Deliverable: The database model should include table names, field names, and identify which fields are primary and foreign keys. Relationships between tables should use the Crow’s Foot method.
When you connect your tables, be sure that the relationship lines are attached and connect to the specific columns on the table. This way, you are showing that a PK or FK on one table is connected to the corresponding PK/FK on the other table. The feet shouldn't be touching random parts of the table.
When ordering the columns in your entities, it's best to list the PKs as the first columns, the FKs as the next ones, and then the rest of your non-key columns below that. This keeps it neat and makes it easy to quickly identify what the table is about.
Remember that a column can be an FK and also part of a composite PK. If that’s the case, you can use "PK,FK" for any columns that are both part of a composite PK and are also an FK.
To get solution of above problem or need any other help related to database design then you can contact us at:
realcode4you@gmail.com
Comments