• All Member IDs are created as 10-digit numeric codes; • All Vehicle codes are

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.

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 latitude

Explanation / Answer

create table country(
   code number,
   name varchar2(100),
   primary key(code)
);

create table member(
   firstname varchar2(100),
   lastname varchar2(100),
   title varchar2(100),
   memberid number,
   primary key (memberid)
);

create table journey(
   start_time timestamp,
   start_date date,
   nbooked number,
   source varchar2(100),
   destination varchar2(100),
  
);

create table vehicle(
   code vachar2(100),
   capacity number,
   primary key (code)
);

create table place(
   sportvenue varchar2(1),
   accomodation varchar2(1),
   address varchar2(100),
   gps varchar2(100),
   longitude number(2,2),
   lattitude number(2,2)
);

create table event(
   name varchar2(100),
   result_type varchar2(100),
   start_date timestamp,
   primary key(name)
);

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.