Notes

/* create tables */

/* rental_status_codes */

CREATE TABLE rental_status_codes(

rental_status_code int IDENTITY(1,1) NOT NULL,

rental_status_description varchar(150) NOT NULL);

/* transaction_types */

CREATE TABLE transaction_types(

transaction_type_code int IDENTITY(1,1) NOT NULL,

transaction_type_description varchar(150) NOT NULL);

/* payment_methods */

CREATE TABLE payment_methods(

payment_method_code int IDENTITY(1,1) NOT NULL,

payment_method_description varchar(150) NOT NULL);

/* financial_transcations */

CREATE TABLE financial_transcations(

transaction_id int IDENTITY(1,1) NOT NULL,

account_id int NOT NULL,

item_rental_id int NOT NULL,

previous_transaction_id int NOT NULL,

transaction_type_code int NULL,

transaction_date datetime NULL,

transaction_amount decimal(18,2) NULL,

transaction_comment varchar(1000) NULL);

/* accounts */

CREATE TABLE accounts(

account_id int IDENTITY(1,1) NOT NULL,

customer_id int not null,

payment_method_code int NOT NULL,

account_name varchar(150) NOT NULL,

account_details varchar(550) NOT NULL);

/* customers */

CREATE TABLE customers(

customer_id int IDENTITY(1,1) NOT NULL,

member_vn int not null,

membership_number int NOT NULL,

date_became_member datetime NOT NULL,

customer_first_name varchar(150) NOT NULL,

customer_last_name varchar(150) NOT NULL,

customer_address varchar(550) NOT NULL,

customer_phone varchar(50) NOT NULL,

customer_email varchar(150) NOT NULL,

customer_dob datetime NOT NULL);

/* customer_rentals */

CREATE TABLE customer_rentals(

item_rental_id int IDENTITY(1,1) NOT NULL,

customer_id int NOT NULL,

movie_id int NOT NULL,

rental_status_code int NOT NULL,

rental_date_out datetime NOT NULL,

rental_date_returned datetime NULL,

rental_amount_due decimal (18, 2) NULL,

other_rental_detail varchar (1000) NULL);

/* create primary keys with ALTER TABLE statement */

ALTER TABLE customer_rentals ADD CONSTRAINT pk_item_rental_id PRIMARY KEY (item_rental_id);

ALTER TABLE transaction_types ADD CONSTRAINT pk_transaction_type_code PRIMARY KEY (transaction_type_code);

ALTER TABLE rental_status_codes ADD CONSTRAINT pk_rental_status_code PRIMARY KEY (rental_status_code);

ALTER TABLE payment_methods ADD CONSTRAINT pk_payment_method_code PRIMARY KEY (payment_method_code);

ALTER TABLE financial_transcations ADD CONSTRAINT pk_transaction_id PRIMARY KEY (transaction_id);

ALTER TABLE accounts ADD CONSTRAINT pk_account_id PRIMARY KEY (account_id);

ALTER TABLE customers ADD CONSTRAINT pk_customer_id PRIMARY KEY (customer_id);

/* create foreign keys */

ALTER TABLE customer_rentals ADD CONSTRAINT fk_customer_rentals FOREIGN KEY(customer_id)

REFERENCES customers (customer_id);

ALTER TABLE customer_rentals ADD CONSTRAINT fk_movie_customer_rentals FOREIGN KEY(movie_id)

REFERENCES movies (movie_id);

ALTER TABLE customer_rentals ADD CONSTRAINT fk_customer_rental_codes FOREIGN KEY(rental_status_code)

REFERENCES rental_status_codes (rental_status_code);

ALTER TABLE financial_transcations ADD CONSTRAINT fk_transactions_account FOREIGN KEY(account_id)

REFERENCES accounts (account_id);

ALTER TABLE financial_transcations ADD CONSTRAINT fk_transactions_rental FOREIGN KEY(item_rental_id)

REFERENCES customer_rentals (item_rental_id);

ALTER TABLE financial_transcations ADD CONSTRAINT fk_transaction_previous FOREIGN KEY(previous_transaction_id)

REFERENCES financial_transcations (transaction_id);

ALTER TABLE financial_transcations ADD CONSTRAINT fk_transaction_codes FOREIGN KEY(transaction_type_code)

REFERENCES transaction_types (transaction_type_code);

ALTER TABLE accounts ADD CONSTRAINT fk_customer_accounts FOREIGN KEY(customer_id)

REFERENCES customers (customer_id);

ALTER TABLE accounts ADD CONSTRAINT fk_customer_payments FOREIGN KEY(payment_method_code)

REFERENCES payment_methods (payment_method_code);