SIMPLE ER DIAGRAM ON AIRLINE DATABASE(S5 CS2 ROLL NO 16)

Leave a comment

TOPIC: IMPLEMENTATION OF A SIMPLE ER DIAGRAM.

QUESTION:

Design an ER diagram for an airline reservation system.

  • The database represents each Airport, keeping its unique Airport Code, the Airport Name, and the City and State in which the Airport is located.
  • Each airline flight has a unique number, the Airline for the flight, and the Weekdays on which the flight is scheduled.
  • A flight is composed of one or more flight legs. Each flight leg has a leg number, Departure airport and Scheduled Departure Time, and an arrival airport and Scheduled Arrival Time.
  • A leg instance is an instance of a flight leg on a specific date. The Number of available seats and the airplane used in the leg instance are also kept.
  • The customer reservations on each leg instance include the customer name, phone, and seat numbers for each reservation.
  • Information on Airplanes and Airplane Types are also kept. For each Airplane type the Type Name, manufacturing company, and maximum number of seats are kept. The Airports in which planes of this type Can Land are kept in the database. For each Airplane, the Airplane Id, Total number of seats, and Type are kept.

SOLUTION:

 

  • Entities

 

  • Airport
  • Airplane Type
  • Airplane
  • Flight leg
  • Flight
  • Leg Instance

  • Relationships

  • It is a 1-N relationship between the airport and flight leg based on departure airport.
  • It is a 1-N relationship between Airport and Flight Leg based on arrival airport.
  • It is a M-N relationship between Airport and Airplane Type based on landing.
  • It is a 1-N relationship between Airplane Type and Airplane based on the type.
  • It is a 1-N relationship between Airplane and Leg instance based on the assignment.
  • It is a 1-N relationship between Leg instance and Seat based on reservation.
  • It is an N-1 relationship between Flight Leg and Flight based on the legs.
  • It is a 1-N relationship between Flight and Fare based on the fares.
  • It is a 1-N relation between Airport and Leg instance based on to where it departs.
  • It is a 1-N relationship between Airport and Leg instance based on the arrival.
  • It is a 1-N relationship between Flight Leg and Leg instance based on instance.relation1relation2
  • Key attributes

    • Airport- Airport code
    • Airplane Type- TypeName
    • Airplane- AirplaneId
    • Leg Instance- Date
    • Seat- SeatNo
    • Fare- Code
    • Flight Leg- LegNo
    • Flight- Number

  • Other relevant attributes

    • Airport- Name, City, State
    • Airplane Type- Max-seats, Company
    • Airplane- Total-no-of-seats
    • Leg Instance- No-of-avail seats
    • Fare- Amount, Restrictions
    • Flight- Weekdays, Airline
  • ER Diagram
  • erdiag

Simple ER diagram roll no:9

Leave a comment

1) The company you work for wants to digitize their time cards, to design the database for submitting and approving time cards. Draw the database ER diagram with the following information:

• A timecard should have hours worked and date submitted

• Each timecard is associated with exactly one employee

• Each timecard should have a unique id

• Each timecard has a status: it is either approved, not approved, or pending

• Each employee has a unique id

• Each employee has a name and address.

• Each employee submits a time card every pay period. i.e. In 1 year, they will submit multiple time cards

• Each employee either has direct deposit or physical check as their method of payment

• Each employee is associated with exactly one manager

• Each manager has a unique id and a name

• Each manager is in charge of multiple employees

• Each manager approves time cards for multiple employees

STEPS IN ER MODELLING

STEP 1) Find the entities.
Time Card, Employee, Manager.
STEP 2) Find the relationship.

p1

pp2

STEP 3) Find the key attributes for every entity.

p3

STEP 4) Complete the ER Diagram

ppo

SIMPLE E_-R DIAGRAM FOR MOTOR VEHICLE LISENCE BRANCH ADMINISTRATION(roll no:2 S5 CS2)

Leave a comment

QUESTION:

MOTOR VEHICLE BRANCH ADMINISTRATION

The motor vehicle branch administrators driving tests and issues driver’s  licenses. Any person who wants driver’s license must first take a learners exam at any motor

vehicle branch in the province. If he/she fails the exam , he can take the exam again any time after a week of the failed exam date, at any branch. If he passes the

exam ,he will be issued a license(learners type) with a unique license number. A learners  license may contain a single restriction on it. The person may take his driver’s

exam at any branch any time before the learners license expiry date(which is usually set at six months after the  license issue date).If he passes the exam, the branch

issues him a driver’s licence .A driver’s license must also record if the driver has completed driver’s education ,for insurance  purpose. Each driver should own a vehicle .If he has no money he can lend it through loan.

Create a E-R diagram following these steps.

1. Find out the entities in the spec.?

2. Find out the relationships among the entities?

3. Find out the attributes of the entities and the relationships?

4. Figure out constraints between entities and relationships?

answer:

1 .Entities:

Driver

Branch

License

Vehicle

Loan

2: IDENTIFY RELATIONSHIPS:

 

 

3 AND 4 :IDENTIFYING KEY ATTRIBUTES AND OTHER RELEVANT ATTRIBUTES

Driver: driver sin, driver name

Branch: branch id, branch name, branch address, branch phone, branch city, branch postalcode.

Vehicle: vehicle no, vehicle type

Loan: loan no, loan type ,loan date

LICENSE:LICENSE NO,LICENSE CLASS,LICENSE EXPIRY.

5 DRAWING ER DIAGRAM

 

 
 

 

 

E-R diagram of televisions series (Roll no.7)

Leave a comment

Question:

Consider the following scenario.

  • There are televisions series, which have names, networks and production companies, and are identified by names
  • A television series has one or more episodes, identified by episode number. Episodes also have a title and a length.
  • There are also movies. A movie is identified by its title. It also has a studio.
  • An actor is identified by name and birth date, and also has a nationality.
  • A writer is also identified by name and birth date, and also has a literary agency that represents him or her.
  • An actor can appear as a “regular” on a television series, a guest star on an episode, and a performer in a movie.
  • An episode has a writer, and a movie has a writer.

Draw an ER diagram that represents this scenario.

ImageImageImageImageImage

SIMPLE ER DIAGRAM-BOOK STOCK MANAGEMENT (RNO 5,S5 CS2)

Leave a comment

TOPIC: Collect a simple ER diagram with explanation

                              BOOK STOCK MANAGEMENT SYSTEM

ER diagram of a book stock management system is given below. Books are written by authors & published by the publishers. Shopping basket contains books. Ware house stocks books. Customer takes book from shopping basket.

  • Author is uniquely identified by Name. It also consists of address, URL.
  • Book is uniquely identified by ISBN. It also consists of year, title and price.
  • Publisher is uniquely identified by Name. It also consists of Address, Phone and URL.
  • Customer is uniquely identified by email. It also consists of Name, address, phone.
  • Shopping basket is uniquely identified by basket id.
  • Ware house is uniquely identified by code. It also consist of address, phone.

Many author writes many books.

A publisher can publish many books.

Many books can be stocked in a warehouse.

A customer can have many shopping baskets.

A shopping basket contains many books.

ANSWER

STEP-1 :-> IDENTIFYING THE ENTITIES

The entities are

  • AUTHOR
  • BOOK
  • PUBLISHER
  • WAREHOUSE
  • SHOPPING-BASKET
  • CUSTOMER

STEP-2 :-> IDENTIFYING THE RELATIONSHIPS

 

Many author writes many books.M:N CARDINALITY

A publisher can publish many books.N:1 CARDINALITY

Many books can be stocked in a warehouse.N:1 CARDINALITY

A customer can have many shopping baskets.1:N CARDINALITY

A shopping basket contains many books.1:N CARDINALITY

STEP-3 :-> IDENTIFYING KEY ATTRIBUTES

AUTHOR NAME

BOOK ISBN

PUBLISHER NAME

WAREHOUSE CODE

SHOPPING-BASKET BASKETID

CUSTOMER EMAIL

 

STEP-4 :-> OTHER RELEVANT ATTRIBUTES

AUTHOR URL, ADDRESS

BOOK YEAR, TITLE, PRICE

PUBLISHER ADDRESS, PHONE, URL

WAREHOUSE ADDRESS, PHONE

CUSTOMER NAME, ADDRESS, PHONE

 

 

 

STEP-5 :-> ER DIAGRAM

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

ER DIAGRAM OF BLOOD BANK MANAGEMENT SYSTEM(S5 CS2, RNO:12)

2 Comments

TOPIC : ER DIAGRAM EXAMPLE WITH EXPLANATION

BLOOD BANK MANAGEMENT SYSTEM

Question :

A Blood Bank stores blood of various blood groups . Many donors donate blood ,each of different blood group/type. A donor may donate blood more than once and he is identified by a donor id(DID),name, sex, age , address and phone number. The blood donated by the donor is characterized by blood type , code and cost. Before each donor donates his blood , he is required to register himself as a donor with the receptionist who works at the Blood Bank. The receptionist is identified by employee id, name , address and phone number. The Blood Banks receives orders for blood from many hospitals for emergency purposes and other surgical requirements and each blood bank issues the same of required blood type. Each blood bank has it’s own blood bank number(BNO) , issues, orders and blood types stored. The Blood Bank is managed by the blood bank manager who is identified by employee id , name , email_id and phone number .He is responsible for the proper management of the blood bank . The hospitals are identified by name, address and phone number. Represent this using an ER diagram.

Answer:

STEP 1 : Identifying the entities

1. Donor
2. Blood
3. Receptionist
4. Blood Bank
5. Blood Bank Manager
6. Hospital

STEP 2 : Identify the relationships.

1.A donor may donate blood any number of times. So cardinality is 1:N.
2.Many donors may register blood donation with a single receptionist. So cardinality is N:1.
3.Blood of different type in large numbers is stored in a single Blood Bank . So cardinality is N:1.
4.Blood Bank is managed by an blood bank -manager. So cardinality is 1:1.
5. A receptionist works in a Blood Bank . So cardinality is 1:1.
6. Hospitals may order blood from different Blood Banks . So cardinality is N:M.

erdj

STEP 3 : Identify the key attributes.

1. Donor – donor id(DID)
2.Blood – code
3.Receptionist – employee id(empid)
4.Blood Bank–Blood Bank number(BNO)
5.Blood Bank Manager – employee id(empid)
6.Hospital – name, phone number(phno)

STEP 4 : Identify other relevant attributes

1. Donor – name, age, sex, address, phone number(phno)
2. Blood – blood type,cost
3. Receptionist – name, address, phone number(phno)
4. Blood Bank– issues, orders , blood type
5. Blood Bank Manager – name ,email _id , phone number(phno)
6. Hospital-address

STEP 5 : Complete the ER Diagram.

finalerd

ER DIAGRAM OF CARGO SHIP MANAGEMENT(S5 CS2 RNO 11)

Leave a comment

ER DIAGRAM OF CARGO SHIP MANAGEMENT.

A crew of people will be controlled by a captain.A vessel can have crew of people but  that crew of people can be present only in one  vessel.In a vessel,the captain has the right to give commands.Vessel can be a SOTL.Many regions are patrolled by vessels and many vessels patrols many regions to make sure that there is no trouble there.

▪ Crew is uniquely identified by name.It also consist of age,rank.

▪Captain is uniquely identified by name.

▪Vessel is uniquely identified by ship name.It also consists of flank speed,  displacement.

▪Region is uniquely identified by region name.

▪SOTL is uniquely identified by class name.It consist of size primary,num_primary.

Draw an ER diagram for the ship cargo database with the various attributes and relationships.

ANSWER:

1.Identify the entities.

→CREW

→CAPTAIN

→REGION

→SOTL

→VESSEL

2.Identify the relationships.

› A CREW IS CONTROLLED BY A CAPTAIN.

CARDINALITY-1:1

›MANY CREWS ARE ASSIGNED TO ONE VESSEL.

CARDINALITY-N:1

›CAPTION GIVE COMMANDS IN A VESSEL.

CARDINALITY-1:1

›MANY REGIONS ARE PAROLLED BY MANY VESSELS.

CARDINALITY-N:N

›VESSEL IS A SOTL.

CARDINALITY-N:N

erd relatn 1

3.Identify key attributes for each entities

> CREW-CREW_NAME

>CAPTAIN-CAP_NAME

>REGION-REGION_NAME

>SOTL-CLASS_NAME

>VESSEL-SHIP_NAME

4.Identify other relevant attributes.

▪CREW

>CREW_NAME

>AGE

>RANK

▪CAPTAIN

>CAPTN_NAME

▪REGION

>REGION_NAME

▪VESSEL

>SHIP_NAME

>FLANK_SPEED

>DISPLACMENT

▪SOTL

>CLASS_NAME

>SIZE_PRIMARY

>NUM_PRIMARY

5.Complete ER diagram.

erd dia2

Older Entries