Parameterized Procedures in SQL Server First, this is for a major assignment. I am not asking any of you far more experienced SQL users to do the assignment for me, but I need your help. SO this project runs similarly to a Netflix model, where you have a customer, a queue, a rental plan, DVDs with directors/producers and genres. What I need help with is twofold. First, I need help figuring out what tables I am missing, and which are missing attributes. I know I need a new table for the site's inventory but am not sure where it should go (one of the requirements is a procedure that adds new movies to inventory, and another needs to find the movies that are out of stock). I attached my ERD and the data I used to create the tables I have so far.


CREATE TABLE customer (
customer_id DECIMAL(12) PRIMARY KEY,
cust_first VARCHAR(50),
cust_last VARCHAR(50),
cust_phone DECIMAL(10),
cust_email VARCHAR(50));

CREATE TABLE rental_plan (
plan_id DECIMAL(12) PRIMARY KEY,
customer_id DECIMAL(12),
description VARCHAR(50));

CREATE TABLE account (
account_id DECIMAL(12) PRIMARY KEY,
customer_id DECIMAL(12),
plan_id DECIMAL(12));

CREATE TABLE queue (
account_id DECIMAL(12),
disc_id DECIMAL(12));

CREATE TABLE dvds (
disc_id DECIMAL(12) PRIMARY KEY,
disc_title VARCHAR(255),
genre_id DECIMAL(12),
producer_id DECIMAL(12)
copies DECIMAL(12));

CREATE TABLE producer (
producer_id DECIMAL(12) PRIMARY KEY,
producer_firstname VARCHAR(255),
producer_lastname VARCHAR(255));

CREATE TABLE genre (
genre_id DECIMAL(12) PRIMARY KEY,
genre_name VARCHAR(255),
description VARCHAR(255));

ALTER TABLE account
ADD CONSTRAINT acc_cst_fk
FOREIGN KEY(customer_id)
REFERENCES customer(customer_id);

ALTER TABLE rental_plan
ADD CONSTRAINT rent_cst_fk
FOREIGN KEY(customer_id)
REFERENCES customer(customer_id);

ALTER TABLE queue
ADD CONSTRAINT queue_acct_fk
FOREIGN KEY(account_id)
REFERENCES account(account_id);

ALTER TABLE queue
ADD CONSTRAINT queue_dvd_fk
FOREIGN KEY(disc_id)
REFERENCES dvds(disc_id);

ALTER TABLE dvds
ADD CONSTRAINT dvd_genre_fk
FOREIGN KEY(genre_id)
REFERENCES genre(genre_id);

ALTER TABLE dvds
ADD CONSTRAINT dvd_prod_fk
FOREIGN KEY(producer_id)
REFERENCES producer(producer_id);


This procedure now works. Thanks!

CREATE PROCEDURE RENT_MOVIE
@account_id_arg DECIMAL(12),
@disc_id_arg DECIMAL(12)
AS
BEGIN
INSERT INTO queue(account_id, disc_id)
VALUES (@account_id_arg, @disc_id_arg);
END;


The requirements I am focused on are parameterized procedures. The next is another procedure that adds a movie to a customer's queue as the next movie to be delivered. The second is a procedure that enrolls customers into specific rental plans. The 3rd is a procedure that adds a certain number of movies to a title's inventory, which is now part of the DVDs table. I can probably handle this one myself now. If anyone wants to assist with these, that would be great, but I just wanted you to be aware of the requirements I need to meet if you do choose to assist me with the two issues listed above.
WRT the procedure - take a second look at the fields and the table you're trying to populate in the INSERT statement; there's a problem there.

I'm trying to figure out why you have an acct_queue_bridge table. This normally is used to maintain a many-to-many relationship. I'm assuming that a queue belongs to one account, not many? Even if an account can have many queues, you'd only need the bridge if one queue ties to multiple accounts.

However, it's true that each account can have many DVDs on its queue, and each DVD could be on many queues. I would eliminate acct_queue_bridge, add account_id to the queue table, and use that as a bridge between account and dvds

I see a few other similar things that I'd question:


Can a customer have multiple accounts? (this is not unusual - allowing an account for each member of the family. However, only include it if the project seems likely to need it).
If the customer has multiple accounts, would there be a separate rental plan for each? I would think not: I would expect there to be a reference table of the different rental plans in use and/or available, and for each customer to sign up for one instance of those plans; in which case, it's possible to store the rental_plan_id in the customer table directly. You might need to track the customer's rental plan history; then you would probably have a separate table with the customer, the plan, and the date range when the plan was active. The current plan could be included there, or it could live in customer, and be copied into a cust_plan_hist table when it changed.
You currently have a queue_id in the dvds table. I would expect one DVD to be able to be in many queues (however, see my next point). I don't see why you'd link a DVD to a specific queue; perhaps I'm missing something here.
I believe that dvds and your inventory table will be closely related, if not one and the same. I'm assuming a row in dvds represents the idea of a movie/show on DVD, not a specific physical disk. in an actual Netflix business, dvd_inv (or whatever) would be where the actual physical disks are represented. You would need that there because each disk will have some sort of inventory number on it, and you'd track the status of each disk: available, shipped to , missing, whatever. You would probably want to track things like the condition of each disk (so if something leaves pristine and comes back scratched up, you can approach your customer about the change), and of course which specific disk is in the hands of which specific customer. However, as this is not necessarily intended to be a "real world" system, I'm not sure if you need to implement that level of detail - you might be able to get away with just having "bucket" fields to hold those numbers in the dvds table itself (3 available, 5 shipped to customers, 1 missing = 9 total).
Note that some instructors will actual object to you designing a more realistic system than they're asking for, because it becomes more difficult for them to assess and grade. Other instructors will applaud the initiative to go above and beyond the minimum requirements. However, once you start that way, if you run into problems later on, you may have to back out and re-implement parts of your solution to meet future parts of the assignment. I'd recommend sticking as close as possible to the actual requirements in school. In the real world, thinking a step or two ahead of your customer is quite valuable during the design phase, and can save your time and their money (something of a double-edged sword, at times, I suppose :-).