extended E-R diagram for sports club(s5cs2 roll no 34)

Leave a comment

EXTENDED E-R DIAGRAM FOR SPORTS CLUB (S5 CS2 ROLL NO 34)

TOPIC: collect one E-R diagram with extended ER features and explanations

QUESTION:

Create an E-R diagram of a sports club conducted by school.

a) A school is decided to setup a sports club outside the school.

b) Sports club can be categorized based on the type: cricket club, football club.

c) A student can join in any one of the sports club.

d) Each sports club has a coach who trains the students.

e) Each student can be identified using id no.

f) Salary, experience, name of the coach can also be included.

ANSWER:

     asha                                                    

STEP 3: IDENTIFY THE KEY ATTRIBUTES

Sports club: clubname

Student: Sid

Cricket: Cplayerid

Football: Fplayerid

Coach: Cid

STEP4: IDENTIFY OTHER RELEVANT ATTRIBUTE

Sports:

Location

Contactno

Student:

Sname

Address

Phone

Cricket:

Cname

Cclass

Football:

Fname

Fclass

Coach:

Name

Experience

Caddress

Salary

asha1

More

Advertisements

Roll No.18 An EER Diagram for a small private airport database.

Leave a comment

 

Figure 1 shows an example of an EER diagram for a small private airport database that is used to keep track of airplanes, their owners, airport employees and pilots.  From the requirements for this database, the following information was collected. Each airplane has a registration number [Reg#], is of a particular plane type [OF- TYPE], and is stored in a particular hangar [STORED-IN]. Each plane type has a model number [Model}, a capacity [Capacity], and a location [Location]. The  database also keeps track of the owners of each plane [OWNS]and the employees who have maintained the plane [MAINTAIN]. Each relationship instance in OWNS relates an airplane to an owner and includes the purchase data [Pdate]. Each relationship instance in MAINTAIN relates an employee to a service record [SERVICE]. Each plane undergoes service many times; hence, it is related by [PLANE-SERVICE] to a number of service records. A service record includes as attributes the date of maintenance [Date], the number of hours spent on the work [Hours], and the type of work done [Workcode].  We use a weak entity type [SERVICE] to represent airplane service, because the airplane registration number is used to identify a serVice record. An owner is either a person or a corporation.  Hence, we use a union category [OWNER] that is a subset of the union of corporation [CORPORATION] and person [PERSON] entity types. Both pilots [PILOT] and employees [EMPLOYEE] are subclasses of PERSON.  Each pilot has specific attributes license number [Lic-Num] and restrictions[Restr]; each employee has specific attributes salary [Salary] and shift worked [Shift].  All person entities in the database have data kept on their social security number [Ssn], name [Name], address[Address], and telephone number [Phone}.For corporation entities, the data kept includes name [Name}, address[Address], and telephone number [Phone}. The database also keeps track of the types of planes each pilot is authorized to fly [FLIES] and the types of planes each employee can do maintenance work on [WORKS-ON]. 

 Image

 

EXTENDED ER DIAGRAM(Art Museum) Roll No: 32, S5CS2

Leave a comment

                                                                       ART  MUSEUM

Design a database to keep track of information for an art museum.Assume that the following requirements were collected:

The museum  has a collection of art_objects.Each art_object has a unique id,an artist(if known),a year(when it was created,if known) and a title.

Art_objects are categorized based on their types.There are two main types:painting and sculpture.

Painting has a paint type and style.

Sculpture has a material from which it was created,height and weight.

Different exhibitions occur each having a name,start date and end date.Exhibitons  are related to all the art objects that were on display on display during the exhibition.

Draw an EER schema diagram for this application.

ANSWER:

Step 1:

Entities:

1.art_object

2.artist

3.painting

4.sculpture

5.exhibition

 

 

Key attributes:

1)Art_object: idno

2)Painting: painttype

3)Sculpture: material

4)Artist:Aname

5)Exhibition:Ename

 

Other attributes:

1)Art_object: year,title.

2)Painting: style.

3)Sculpture: height,weight.

4)Artist: country,mainstyle

5)Exhibition: sdate,edate.

 

 RELATIONSHIPS:

 

Image

 

ER DIAGRAM:

 

Image

 

 

EXTENDED ER-DIAGRAM (Insurance Company) Roll No; 29 S5CS2

Leave a comment

                                                                INSURANCE COMPANY

Design  a database for insurance company.Asume that following are the requirements that were collected:

An insurance company has different policies.Policies have pno,term_price and coverage.

Policies are categorized based on their types.There are two types:Auto_policy and Home_policy.

Policies for vehicles come under Auto policy.Auto_policy has pno,vehicle type and issue date.

Policies for house come under home policy.Home_policy has pno,issue date and term_price.

Customers take policies policy through policy agent.A customer can take only one policy .

ANSWER

ENTITIES:

1)Policy

2)Auto_policy

3)Home_policy

4)Vehicle

5)House

6)Customer

 

KEY ATTRIBUTES:

1)Policy: pno

2)Auto_policy: polno

3)Home_policy:policyno

4)House:hno

5)Vehicle: vehicle_no

6)Customer: custid

OTHER ATTRIBUTES:

1)Policy:price,coverage.

2)Auto_policy:type,issue_date

3)Home_policy:date,term_price

4)House:name

5)Vehicle:model

6)Customer:address,phno

 

RELATIONSHIPS:

 

Image

 

ER DIAGRAM:

 

Image

 

 

 

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

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

 

Older Entries