Question

1. Convert the ER diagram below into a set of tables and schema's in SQL code. and Create the following into tables. Except for one case you should have a total of 15 tables. The exception is both observation and it's Associate enity Taken are one table

Answer 1

The ER Model is focussed to be a description of real-world entities. Although its development/construction is done in such a way as to allow fair and easy translation to the relational schema model, this is not fully trivial process. The ER diagram represents the conceptual level of database design while the relational schema represents the logical level for the database design. Following simple rules are followed:

1. For an entity(represented by rectangular boxes in ER diagram) set, create a table with the same attributes and candidate key or primary key.
2. For relationships, create a table with primary keys of two tables and attribute of connected realtion if any.

ADDRESS( cityAddress, city, state ) candidate key:cityAddress, city , state

PERSON( pid,name ) candidate key: pid

  OBSERVATION (oid, date, temperature, humidity, precip. ,city), candidate key: oid foreign key: city

OBSERVER ( pid ), candidate key: pid

METEREOLOGIST( pid ), candidate key: pid

CONSUMER( pid ), candidate key: pid

WARNINGS( wwid,description,date,city) candidate key: wwid

FORECAST( fid,city,date,low,high) candidate key: fid

WEATHERPROGRAM( wid,name ) candidatekey; wid

LIVESAT(city, streerAddress, state,pid)

   RECORDS(oid,pid,method)

   ISSUES(wwid.pid)

   PRESENTS(fid,pid)

VIEWS(pid,fid)

MAKES(wid,fid,oid)

15 tables will be created in all along with relationship tables

and only 9 tables if relationships are shown as foreign keys

CREATE TABLE IF NOT EXISTS Person (
  pid int(6) unsigned AUTO_INCREMENT,
  name varchar(),
  PRIMARY KEY (pid)
);

CREATE TABLE IF NOT EXISTS Address (
   state varchar(10),
streetAddress varchar(20), 
city varchar(20) unsigned AUTO_INCREMENT,
  PRIMARY KEY (city),
);

CREATE TABLE IF NOT EXISTS Observation (
  oid int(5) unsigned AUTO_INCREMENT,
  date int(10),
temperature int(5),
humidity int(5),
precipitation int(5),
  PRIMARY KEY (oid),
FOREIGN KEY(city) REFERENCES ADDRESS( city)
);

CREATE TABLE IF NOT EXISTS Metereologist (
  pid int(4) unsigned AUTO_INCREMENT,
  
  PRIMARY KEY (pid),
);

CREATE TABLE IF NOT EXISTS consumer(
  pid int(4) unsigned AUTO_INCREMENT,
  
  PRIMARY KEY (pid)
);

CREATE TABLE IF NOT EXISTS Observer(
  pid int(4) unsigned AUTO_INCREMENT,
 
  PRIMARY KEY (pid)
);

CREATE TABLE IF NOT EXISTS Forecast (
  fid int(4) unsigned AUTO_INCREMENT,
  city varchar(10),
date int(10),
low int(5),
high int(5),
  PRIMARY KEY (fid),
FOREIGN KEY (fid) REFERENCES metereologist(pid),

FOREIGN KEY (fid) REFERENCES consumer(pid),

FOREIGN KEY (fid) REFERENCES observer(pid),

);

CREATE TABLE IF NOT EXISTS weather program(
  wid int(4) unsigned AUTO_INCREMENT,
name varchar(10),
 
  PRIMARY KEY (wid),
FOREIGN KEY( wid) REFERENCES FORECAST (fid),
);