SEIS 630 DBMS Conceptual model creation
|
FKey_E |
Table to connect Entity to Attribute as a F Key | |||||||||||||||
|
FName_E |
EName |
AName | ||||||||||||||
|
PKey_E |
Table to connect Entity to Attribute as a P Key | |||||||||||||||
|
PName_E |
EName |
AName | ||||||||||||||
|
Entity |
Table for Entities | |||||||||||||||
|
EName |
PName |
FName |
AName |
RName |
ISA | |||||||||||
|
Relationship |
Table for relationships | |||||||||||||||
|
RName |
PName |
N_Number |
M_Number |
EName |
FName | |||||||||||
|
FKey_R |
Table to connect Relationship to Attribute as a F Key | |||||||||||||||
|
FName_R |
RName |
AName | ||||||||||||||
|
PKey_R |
Table to connect Relationship to Attribute as a P Key | |||||||||||||||
|
PName_R |
RName |
AName | ||||||||||||||
|
Attribute |
Table for attributes | |||||||||||||||
|
AName | ||||||||||||||||
Conceptual Model Creation
|
ISA Relationship | ||||
|
RName |
EName |
EName |
CREATE TABLE FKey_E(
FName_E CHAR(20),
EName CHAR(20),
AName CHAR(20),
PRIMARY KEY(FName_E) UNIQUE(FName, EName))
CREATE TABLE PKey_E(
PName_E CHAR (15),
EName CHAR (20),
AName CHAR (20),
PRIMARY KEY(PName_E),
UNIQUE(PName_E, EName))
CREATE TABLE Entity(
EName CHAR(20),
PName CHAR(15), FName CHAR(20), AName CHAR(20),
RName CHAR(50),
ISA CHAR(20)
PRIMARY KEY(EName),
FOREIGN KEY(ISA)
UNIQUE(EName,PName))
CREATE TABLE FKey_R
FName_R CHAR(20),
EName CHAR(20),
AName CHAR(20),
PRIMARY KEY(FName_R),
UNIQUE(FName_R, EName))
CREATE TABLE PKey_R
PName_R CHAR (15),
EName CHAR (20),
AName CHAR (20),
PRIMARY KEY(PName_R),
UNIQUE(PName_R, EName))
CREATE TABLE Relationship
RName CHAR(50)
PName CHAR(15),
N_Number INTEGER,
EName CHAR(20),
FName CHAR(20)
PRIMARY KEY(RName),
UNIQUE(RName, AName, EName))
CREATE TABLE Attribute(
AName CHAR(20),
PRIMARY KEY(AName))
CREATE TABLE FKey_E(
FName_E CHAR(20),
EName CHAR(20),
AName CHAR(20),
PRIMARY KEY(FName_E),
FOREIGN KEY(EName) REFERENCES Entity(EName),
FOREIGN KEY(AName)) REFERENCES Attribute(AName),
UNIQUE(FName, EName))
CREATE TABLE PKey_E(
PName_E CHAR (15),
EName CHAR (20),
AName CHAR (20),
PRIMARY KEY(PName_E),
FOREIGN KEY(EName) REFERENCES Entity(EName),
FOREIGN KEY(AName)) REFERENCES Attribute(AName),
UNIQUE(PName_E, EName))
CREATE TABLE Entity(
EName CHAR(20),
PName CHAR(15), FName CHAR(20),
AName CHAR(20),
RName CHAR(50),
ISA CHAR(20)
PRIMARY KEY(EName),
FOREIGN KEY(PName)) REFERENCES PKey_R(PName),
FOREIGN KEY(FName)) REFERENCES FKey_R(FName),
FOREIGN KEY(AName)) REFERENCES Attribute(AName),
FOREIGN KEY(RName)) REFERENCES Relationship(RName),
FOREIGN KEY(ISA)) REFERENCES Entity(ISA),
UNIQUE(EName,PName))
CREATE TABLE FKey_R
FName_R CHAR(20),
EName CHAR(20),
AName CHAR(20),
PRIMARY KEY(FName_R),
FOREIGN KEY(EName) REFERENCES Entity(EName),
FOREIGN KEY(AName)) REFERENCES Attribute(AName),
UNIQUE(FName_R, EName))
CREATE TABLE PKey_R
PName_R CHAR (15),
EName CHAR (20),
AName CHAR (20),
PRIMARY KEY(PName_R),
FOREIGN KEY(EName) REFERENCES Entity(EName),
FOREIGN KEY(AName)) REFERENCES Attribute(AName),
UNIQUE(PName_R, EName))
CREATE TABLE Relationship
RName CHAR(50)
PName CHAR(15),
N_Number INTEGER,
EName CHAR(20),
FName CHAR(20)
PRIMARY KEY(RName), FOREIGN KEY(PName),
FOREIGN KEY(EName) REFERENCES Entity(EName),
FOREIGN KEY(FName)) REFERENCES FKey_R (FName),
UNIQUE(RName, AName, EName))
CREATE TABLE Attribute(
AName CHAR(20),
PRIMARY KEY(AName))
