-- John Wilkinson (MS50) -- Databases (COMS 30103) - Dr. J.Gallagher -- Video Shop - Assignment 2 ------------------------------------------------------------------------ -- video_schema.sql - Video shop schema ------------------------------------------------------------------------ -- Video table (each shop has its own table) create table Video ( Vtitle varchar(30) not null, -- Video Title Vlimits varchar(2) check (vlimits in ('U', '18')), InStock char(1) default 'y', -- Whether in stock primary key (Vtitle)); -- Shop (one tuple per shop). create table Shop ( ShopName varchar(30) not null, -- name of shop Manager varchar(30) not null, -- name of manager primary key (ShopName)); -- Member table - complete table replicated at each shop create table Member ( MemberId char(6) not null, -- member's ID MemberName varchar(30), -- member's name MemberDOB date, -- member's date of birth MemberCount number default 0, -- number of videos held primary key (MemberId)); -- Rental table (each shop has its own table) create table Rental ( RentalNum number not null, -- rental number VideoTitle varchar(30) not null, -- video rented MemberId char(6) not null, -- member's ID DueDate date not null, -- date due back (2 weeks after -- renting date) RentalShop varchar(30) not null, -- shop name Returned char(1) default 'n', -- whether returned primary key (RentalNum), constraint Rental_Video foreign key (VideoTitle) references Video(Vtitle), constraint Rental_Shop foreign key (RentalShop) references Shop(ShopName), constraint Rental_Member foreign key (MemberId) references Member(MemberId) ); create table message_s (logmessage_s varchar(80)); -- Grant permission to Nakeesan to access my directory to update Member table: grant select, update, delete, insert on Member to nakeesan;