ER DIAGRAM WITH EXTENDED FEATURES (ROLL NO : 24)

Leave a comment

 TOPIC 

COLLECT AN ER DIAGRAM  WITH  EXAMPLE (MUST CONTAIN ONE EXTENDED FEATURE).

QUESTION

A car selling shop sells variety of cars. Shop consists of two types of employee. They are salesman and mechanic. Complete an ER Diagram based on the information given below:

a)   Each employee  have employee number and name. Employe is identified using employee number.

b)   Each car sold by the shop had a car licence.Car is also specified for its manufacturer,model,value.

c)    Salesman buy a particular car by noting its date,price and value

d)   Salesman sells car by noting down the date,  commission ,values to the clients.

e)    Clients are the persons who buys car from the salesman.Client ID will refer  for each client. Salesman note down  client   name,address,phone for future use.

f)     Car shop will also take care of the repair job and is done by the mechanic.

g)   Each repair job is recorded by repair number, cost,description.Cost for repair job is calculated by the work done and the parts got repaired/changed.

 IDENTIFYING THE ENTITIES

a)    EMPLOYEE

 

b)    MECHANIC

 

c)    SALESMAN

 

d)    REPAIR JOB

 

e)    CAR

 

f)      CLIENT

 IDENTIFYING THE PRIMARY KEYS

a)    EMPLOYEE    >>  NUMBER

b)    REPAIR JOB  >>   NUMBER

c)     CAR               >>   LICENCE

d)    CLIENT         >>     ID

IDENTIFYING OTHER RELEVANT ATTRIBUTES.

a)    EMPLOYEE   >>   NAME

b)    REPAIR JOB  >>   DESCRIPTION , COST

c)     CAR              >>   MANUFACTURER , MODEL , YEAR

d)    CLIENT         >>   NAME , PHONE , ADDRESS

RELATIONSHIPS 

relation1

relation 2

ERDIA

ER DIAGRAM WITH EXTENDED FEATURE, ROLL NO 33

Leave a comment

TOPIC   : EXAMPLE OF  EXTENDED ER  DIAGRAM  WITH  EXPLANATION

HOSPITAL MANAGEMENT

There will be many employees in the hospital. Employees can be doctors, nurses, receptionist .Doctors will attend the patients .Doctor can be a trainee  , permanent or  a visiting doctor . A doctor can attend many patients. A patient is uniquely identified by patient id .It is  also characterized  by  patient  details,address, sex, name  and contact number .Patient details includes  date of admitted and date of discharge .Each person is assigned a room at a particular time duration .These rooms are governed by the nurses .Rooms is characterized by room  type and period .It is uniquely identified by room id. Records are kept for every patients .These records are maintained by receptionist .Each record is uniquely identified by the record number .It also has  the details  about  appoint ,patient id, description. Bills are generated  for both treatment and medicine provided to the patient .

STEP   1 : IDENTIFYING ENTITIES

  • Patient
  • Doctor
  • Rooms
  • Nurse
  • Employee
  • Record
  • Receptionist
  • Trainee
  • Visiting
  • Permanent

STEP   2  : IDENTIFYING RELATIONSHIPS

  • Ø A doctor can attend many patients. So cardinality  is 1:N.
  • Ø Each patient is assigned a room .So cardinality is 1:1
  • Ø Many nurse governs  each room .So cardinality is M:1
  • Ø A receptionist maintains many number of records .So cardinality is 1:N
  • Ø Every  patient  is paid bill for the treatment .So cardinality is 1:N
  • Ø Every patient is paid  bill for the medicines .So cardinality is 1:N
  • ER_PICTURE
  • STEP  3 : IDENTIFYING KEY ATTRIBUTES
    • Patient  – patient ID(PID)
    • Record  -Record no
    • Employee – Employee ID (EID)
    • Rooms – Room ID
    • Medicine –Code

    STEP 4 : IDENTIFYING OTHER RELEVANT ATTRIBUTES

    • Patient –

    Name, Sex, Patient details (pdetails), address , contact no

    • Record –

    Appointment , patient_id ,description

    • Employee –

    Salary, E_address ,sex ,NID ,E_name ,History ,Contact no

    • Rooms –

    Room type  , Period

    • Medicine –

    Quantity  ,Price

    STEP 5 : COMPLETED ER DIAGRAMS

    h7ithf1smk1

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

ER diagram with extended features , roll no:22

Leave a comment

ER DIAGRAM WITH EXTENDED FEATURES (ROLL NO:22)

Question:

The Motor Vehicle Branch administers driving tests and issues driver’s licenses.

Any person who wants a driver’s license must ¯rst take a learner’s 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 is issued a license (type = learner’s) with a unique license

number. A learner’s license may contain a single restriction on it. The person

may take his driver’s exam at any branch any time before the learner’s 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 license. A driver’s license

must also record if the driver has completed driver’s education, for insurance

purposes.

Create a E-R diagram.

 

Answer:

 STEPS:

1)ENTITIES AND ATTRIBUTES

a)learner_license

  • Driver_ed

b)driverlicense

  • restrictions

c)license

  • license_class
  • license_expiry
  • license_no

d)driver

  • driver_sin
  • driver_birthdate
  • driver_addr
  • driver_city
  • driver_postalcode
  • dirver_phoneno

e)branch

  • bank_id
  • bank_name
  • bank_addr
  • bank_postalcode
  • bank_city
  • bank_phoneno

2)RELATIONSHIPS

1)

Capturenna

mma

 

Completed ER diagram

 

ppp

 

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Older Entries Newer Entries