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

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

 

 

 

MAPPING E-R TO RELATIONAL (ROLLNO:40,S5CS2)

Leave a comment

MUSICIAN RECORD

 

NOTOWN RECORD HAS DECIDED TO STORE INFORMATION ABOUT MUSICIANS WHO PERFORMED ON ITS ALBUMS IN A DATABASE .EACH MUSICIANS IDENTIFIED BY AN SSN, A NAME, AND AN AGE.EACH MUSICIANS LIVED IN DIFFERENT PLACES. PLACES ARE IDENTIFIED BY AN ADDRESS AND A PH.NO. EACH INSTRUMENTS IS IDENTIFIED BY A NAME AND A KEY .EACH ALBUM HAS A TITLE ,DATE AND A FORMAT.EACH SONG HAS A TITLE AND AN AUTHOR. EACH MUSICIANS PLAYED SEVERAL INSTRUMENTS AND A GIVEN INSTRUMENT PLAYED BY SEVERAL  MUSICIONS .EACH ALBUM HAS NO:OF SONGS ON IT, BUT NO SONG MAY APPEAR ON MORE THAN ONE ALBUM .EACH SONG IS PERFORMED BY ONE OR MORE MUSICIAN S AND A MUSICIAN MAY PERFORM A NO:OF SONGS.EACH ALBUM HAS EXACTLY ONE MUSICIAN  WHO ACTS ITS PRODUCER.A MUSICIAN MAY PRODUCE SEVERAL ALBUMS.

 

 

 

 

 

 

 

 

 

1.0           MAPPING TO RELATIONAL MODEL

 

1.1 ENTITIES

 

 

1.1.1 MUSICIAN

 

SSN

AGE

M_NAME

 

 

 

 

 

 

 

1.1.2 PLACE

 

PH_NO

ADRESS

 

 

 

 

 

1.1.3 ALBUM

 

TITLE

FORMAT

DATE

 

 

 

 

 

                 

 

                 1.1.4  SONG

S_TITLE

AUTHOR

 

 

 

 

 

 

 

 

       1.1.5 INSTRUMENT

NAME

KEY

 

 

 

 

1.2    RELATIONSHIPS

 

 

 

1.2.1  LIVES

SSN

ADDRESS

 

 

 

 

1.2.2  PRODUCE

SSN

TITLE

 

 

 

 

 

1.2.3 APPEAR

TITLE

S_TITLE

   

 

 

 

 

1.2.4 PERFORM

SSN

S_TITLE

 

 

 

 

 

 

1.2.5 PLAY

SSN

NAME

   

 

 

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

MAPPING ER MODEL TO RELATIONAL MODEL-ORDERS AND PRODUCTS(RNO 51,S5CS2)

Leave a comment

ORDERS AND PRODUCTS

THE ER DIAGRAM FOR ORDERS AND PRODUCTS IS GIVEN BELOW

  • There are many workers working under a project.
  • Each worker is identified by name,workerid and by address.
  • The project may include several departments under which many products are being produced.
  • Several workers may belong to single department and may participate in many projects.
  • Each department is been identified by name,department id and by address.
  • Similarly the projects have got it’s own project id and name.
  • The products too got their own name and product id.
  • The project may include several buyorders that may contain the product details and suppliers who delivers the buy orders.
  • The buyorders are specified by their ordered,orderdate and deliverydate.Same way,suppliers are specified by their name and supplier id.

ER DIAGRAM

RELATIONAL MODEL

 

 

Older Entries