Construct a conceptual ORM model, translate that to a Relational Schema, then translate that into SQL table(s). The tables will then be populated with supplied data ( given at the conceptual level) and a few queries specified.

Consider the scenario where we want to model Cities within Counties and Counties within States, here in the US.

Given the facts below

Note: The facts below are not elementary since they can be broken down: However, extract from them the elementary facts you need to construct an ORM diagram, complete with constraints: uniqueness and mandatory. Insert the predicate verbalizations below the fact types, one way of reading is sufficient, that is, you don't need to be able to read it in both directions.

City, County, and State names are Varchar(20), population is Integer and Elevation is Integer

Note: there is more information below than you may need to enter into your table response ( but the data is interesting and I got it from government sources, slightly edited for population growth)!

0. Use your last name for a state, population of 300000. ( this will be needed when I ask you to construct a state table) together with the state names given below.

1. City with CityName of Phoenix is part of County named MaricopaCounty which is part of Arizona

Population of State Arizona is 7500000

Population of Maricopa County is : 4600000

Population of Phoenix is 1640000

Elevation is 1124 ft

2. City with CityName of Tucson is part of County named PinalCounty which is part of Arizona

Population of Pinal county is: 1000000

Population of Tucson is 500000

Elevation is 2640 ft

3. City with CityName of Flagstaff is part of County named CoconinoCounty which is part of Arizona

Population of Coconino county is: 1434000

Population of Flagstaff is 72000

Elevation of Flagstaff is 6900 ft

4. City with CityName Albuquerque is part of a County named Bernalillo which is part of New Mexico

Population of State New Mexico is — 2200000

Population of Bernalillo county is: 662000

Population is 845000

elevation is 5250 ft

Q1( 40 pts) Construct an ORM Diagram complete with

1. Uniqueness and Mandatory constraints

2. Can some entities be combined?

3. Show the predicate verbalization underneath the fact types

Note: This diagram can be hand drawn if necessary.

Q2(30 pts) Construct a Relational Schema for the ORM diagram. Use "PK" for primary key and "FK" for foreign key. If you can do this automatically, that is fine, just cut and paste in the image, otherwise hand drawn is ok.

Q3( 20pts )

a. Only create the State table in your database, defining its attributes, and key(s)

b. Insert into the "State" table the relevant State data above, including your own named state!

Q4( 10 pts) Do a Select all on the State table. Show your result

*****************************************

Q1 ORM-Halpin

Q2 Relational Schema

Q3 Create table & insert data

Q4 Query it