• All Member IDs are created as 10-digit numeric codes; • All Vehicle codes are
ID: 3829423 • Letter: #
Question
• All Member IDs are created as 10-digit numeric codes;
• All Vehicle codes are created as 8-digit alphanumeric codes;
• The medal attribute on the participates relationship can be ‘gold’, ‘silver’ or ‘bronze’, or left as NULL if no medal was received.
Expectations
A credit-level solution should show sound knowledge of logical database implementation by including:
• Tables and attributes with suitable data types to capture all information in the model;
• Appropriate key constraints (PRIMARY KEY, UNIQUE) for all tables;
• Correct foreign key constraints ON DELETE clauses where suitable;
• Appropriate additional integrity constraints expressed by means of, e.g., NOT NULL, DEFAULT or CHECK clauses;
• INSERT statements to populate each relation with at least one record, to demonstrate a database instance consistent with the ER model. 1 For Distinction/High Distinction level submissions, proficiency can be demonstrated through appropriate use of the following features:
• examples of DML statements to demonstrate functionality of integrity constraints (put in a separate file to your main DML statements)
• an assertion definition (commented out in your DDL) to constrain the nbooked attribute of Journey to be kept consistent with the number of bookings make for that journey, and a trigger definition giving equivalent functionality;
• a view to present all details of an athlete (including those inherited from Member), plus columns reporting the number of gold, silver and bronze medals received by that athlete.
• advanced domain constraints, e.g., using regular expressions.
Show transcribed image textname given family title start time Country from books, nbooked when disjoint ISA. code Athlete Staff Official medal participates resul runs role Sport for held at result type Start ime date tart date Vehicle capacity lives in gps longitude latitude
Could someone please improve this DDL?
create table Country(
code char(2),
Name varchar(100) NOT NULL,
PRIMARY KEY(code)
);
create table Member(
member_id INTEGER PRIMARY KEY,
first_name CHARACTER VARYING(45) NOT NULL,
Last_name CHARACTER VARYING(45) NOT NULL,
title varchar(100) NOT NULL,
live_in varchar(50)
Country_code char(2) NOT NULL REFERENCES Country(code)
ON DELETE CASCADE
ON UPDATE CASCADE
);
create table Athlete(
id integer PRIMARY KEY NOT NULL REFERENCES Member(member_id)
ON DELETE NO ACTION
ON UPDATE CASCADE
);
create table Official(
id integer PRIMARY KEY NOT NULL REFERENCES Member(member_id)
ON DELETE NO ACTION
ON UPDATE CASCADE
);
create table Staff(
id integer PRIMARY KEY NOT NULL REFERENCES Member(member_id)
ON DELETE NO ACTION
ON UPDATE CASCADE
);
Create table Books(
when timestamp NOT NULL,
member_id integer NOT NULL REFERENCES Member(member_id),
start_time timestamp NOT NULL REFERENCES Journey(start_time),
start_date date NOT NULL REFERENCES Journey(start_date),
byStaff integer NOT NULL REFERENCES Staff(id)
);
create table Journey(
start_time timestamp PRIMARY KEY,
start_date date PRIMARY KEY,
member_id integer PRIMARY KEY REFERENCES Member(member_id),
nbooked integer NOT NULL,
departure varchar(100) NOT NULL REFERENCES Place(name),
arrival varchar(100) NOT NULL REFERENCES Place(name),
code char(8) NOT NULL REFERENCES Vehicle(code)
);
create table Vehicle(
code char(8) PRIMARY KEY,
capacity varchar(100) NOT NULL
);
create table Place(
name varchar(50) PRIMARY KEY,
address varchar(100) NOT NULL,
longitude varchar(100) NOT NULL,
latitude varchar(100) NOT NULL
);
create table SportVenue(
name varchar(50) NOT NULL REFERENCES Place(name)
);
create table Accommodation(
name varchar(50) NOT NULL REFERENCES Place(name)
);
create table Event(
name varchar(100) PRIMARY KEY,
result_type varchar(100) NOT NULL,
time timestamp NOT NULL,
date date NOT NULL,
sport_name varchar(50) NOT NULL REFERENCES Sport(name),
);
create table Participates(
medal varchar(6) DEFAULT ‘NULL’ CHECK(value IN ’gold’, ’silver’, ‘bronze’),
result varchar(100) NOT NULL,
id integer REFERENCES Athlete(id),
sport_name REFERENCES Event(name)
);
create table Runs(
role varchar(100) NOT NULL,
id integer REFERENCES Official(id),
sport_name REFERENCES Event(name)
);
create table Sport(
name varchar(50) PRIMARY KEY
);
name given family title start time Country from books, nbooked when disjoint ISA. code Athlete Staff Official medal participates resul runs role Sport for held at result type Start ime date tart date Vehicle capacity lives in gps longitude latitudeExplanation / Answer
create table Country(
code varchar2(2) NOT NULL PRIMARY KEY,
Name varchar2(100) NOT NULL
);
create table Member(
member_id NUMBER(8) PRIMARY KEY,
first_name VARCHAR2(45) NOT NULL,
Last_name VARCHAR2(45) NOT NULL,
Nick_Name VARCHAR2(15),
title varchar(10) NOT NULL,
live_in varchar(50)
Country_code VARCHAR2(2) NOT NULL REFERENCES Country(code)
ON DELETE CASCADE
ON UPDATE CASCADE
);
create table Athlete(
id integer PRIMARY KEY NOT NULL REFERENCES Member(member_id),
event EVENT( sport_name)
ON DELETE NO ACTION
ON UPDATE CASCADE
);
create table Official(
id integer PRIMARY KEY NOT NULL REFERENCES Member(member_id)
ON DELETE NO ACTION
ON UPDATE CASCADE
);
create table Staff(
id integer PRIMARY KEY NOT NULL REFERENCES Member(member_id)
ON DELETE NO ACTION
ON UPDATE CASCADE
);
Create table Books(
when timestamp NOT NULL,
member_id integer NOT NULL REFERENCES Member(member_id),
start_time timestamp NOT NULL REFERENCES Journey(start_time),
start_date date NOT NULL REFERENCES Journey(start_date),
byStaff integer NOT NULL REFERENCES Staff(id)
);
create table Journey(
start_time timestamp PRIMARY KEY,
start_date date PRIMARY KEY,
member_id integer PRIMARY KEY REFERENCES Member(member_id),
nbooked integer NOT NULL,
departure varchar(100) NOT NULL REFERENCES Place(name),
arrival varchar(100) NOT NULL REFERENCES Place(name),
code char(8) NOT NULL REFERENCES Vehicle(code)
);
create table Vehicle(
code char(8) PRIMARY KEY,
capacity varchar(100) NOT NULL
);
create table Place(
name varchar(50) PRIMARY KEY,
address varchar(100) NOT NULL,
longitude varchar(100) NOT NULL,
latitude varchar(100) NOT NULL
);
create table SportVenue(
name varchar(50) NOT NULL REFERENCES Place(name)
);
create table Accommodation(
name varchar(50) NOT NULL REFERENCES Place(name)
);
create table Event(
name varchar(100) PRIMARY KEY,
result_type varchar(100) NOT NULL,
time timestamp NOT NULL,
date date NOT NULL,
sport_name varchar(50) NOT NULL REFERENCES Sport(name),
);
create table Participates(
medal varchar(6) DEFAULT ‘NULL’ CHECK(value IN ’gold’, ’silver’, ‘bronze’),
result varchar(100) NOT NULL,
id integer REFERENCES Athlete(id),
sport_name REFERENCES Event(name)
);
create table Runs(
role varchar(100) NOT NULL,
id integer REFERENCES Official(id),
sport_name REFERENCES Event(name)
);
create table Sport(
name varchar(50) PRIMARY KEY
);
This is all I found in your tables and additional you can add foreigns key in your DB for refreance in your other tables.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.