CREATE TABLE account (
       account_id           int NOT NULL,
       PRIMARY KEY (account_id)
)
go


CREATE TABLE member (
       member_id            char(6) NOT NULL,
       account_id           int NULL,
       birthday             datetime NULL,
       customer_name        char(32) NULL,
       phone_number         numeric(10) NULL,
       address              char(32) NULL,
       PRIMARY KEY (member_id), 
       FOREIGN KEY (account_id)
                             REFERENCES account
)
go


CREATE TABLE movie (
       upc_movie            int NOT NULL,
       title                char(32) NULL,
       rating               char(5) NULL,
       total_movies         int NULL,
       times_rented         int NULL,
       bought_movie         datetime NULL,
       release_date         datetime NULL,
       PRIMARY KEY (upc_movie)
)
go


CREATE TABLE rental_movie (
       upc_movie            int NOT NULL,
       member_id            char(6) NOT NULL,
       movie_rentdate       datetime NOT NULL,
       movie_returndate     datetime NULL,
       movie_returned       datetime NULL,
       PRIMARY KEY (upc_movie, member_id, movie_rentdate), 
       FOREIGN KEY (member_id)
                             REFERENCES member, 
       FOREIGN KEY (upc_movie)
                             REFERENCES movie
)
go


CREATE TABLE dvd_player (
       upc_player           int NOT NULL,
       manufacturer         char(32) NULL,
       bought_player        datetime NULL,
       serial_number        int NULL,
       total_players        int NULL,
       rented_count         int NULL,
       PRIMARY KEY (upc_player)
)
go


CREATE TABLE rental_player (
       upc_player           int NOT NULL,
       member_id            char(6) NOT NULL,
       player_rentdate      datetime NOT NULL,
       player_returndate    datetime NULL,
       player_returned      datetime NULL,
       PRIMARY KEY (upc_player, member_id, player_rentdate), 
       FOREIGN KEY (member_id)
                             REFERENCES member, 
       FOREIGN KEY (upc_player)
                             REFERENCES dvd_player
)
go


CREATE TABLE category (
       category_id          int NOT NULL,
       category_name        char(32) NULL,
       PRIMARY KEY (category_id)
)
go


CREATE TABLE credit (
       credit_id            int NOT NULL,
       cardholder           char(32) NULL,
       cardnumber           numeric(16) NULL,
       PRIMARY KEY (credit_id)
)
go


CREATE TABLE account_credit (
       account_id           int NOT NULL,
       credit_id            int NULL,
       PRIMARY KEY (account_id), 
       FOREIGN KEY (credit_id)
                             REFERENCES credit, 
       FOREIGN KEY (account_id)
                             REFERENCES account
)
go


CREATE TABLE movie_categories (
       category_id          int NOT NULL,
       upc_movie            int NOT NULL,
       PRIMARY KEY (category_id, upc_movie), 
       FOREIGN KEY (upc_movie)
                             REFERENCES movie, 
       FOREIGN KEY (category_id)
                             REFERENCES category
)
go




Create Trigger EnforceAge
on rental_movie
for insert, update 
AS 
IF EXISTS 
(
select movie.upc_movie, rental_movie.member_id, memberage.age, movie.rating 
FROM rental_movie, memberage, movie 
WHERE (rental_movie.member_id = memberage.member_id) and movie.rating = 'NC17' and memberage.age < 18 and rental_movie.upc_movie = movie.upc_movie
)
 BEGIN 
RAISERROR('you must be 18 or older to rent this movie',15,1) 
ROLLBACK TRAN 
END


CREATE VIEW MemberAge AS 
select datediff(year, member.birthday, getdate()) as Age, member_id  from member