This is your technical specification that you must follow $100\%$: $($image$)$

You'll use DROP TABLE statements, as in the assignment script supplied.

You'll use CREATE TABLE statements, as well as setting the Primary Keys.

You'll use the ALTER TABLE statements to create the Foreign Keys.

After you've created your tables, issue INSERT INTO statements to get some fake data in them.

Make sure you have enough data to test various scenarios.

BARE MINIMUM for all tables is six $(6)$ records, especially for the leaf $($ending$)$ tables. The middle tables $($and composites$)$ should have more.

The data you use can all come from your imagination. No need to pull or look up data from anywhere. That said, if you want to$,$ feel free.

I am requesting that you follow a few specific INSERT requirements:

Make sure that at least $1$ Viewer does NOT have a best friend.

Make sure that at least $1$ Viewer is the best friend for more than one other viewer.

Be sure to INSERT NULL values for some $($not all$)$ INSERTs for tables that have NULLable fields.

Finally, issue SELECT $*$ FROM statements on each of your newly populated tables. Make sure to use an alias for the table name!

HINTS:

For a Unary relationship, like Viewer$/$BestFriend$,$ make sure that BestFriendID is an INT and NULLable.

When you create the FK of Viewer to BestFriend $($which is still Viewer$),$ remember that the BestFriendID will REFERENCE the ViewerID in the same table.

For single$-$row INSERT statements:

When you INSERT data into Viewer, be sure to set BestFriendID to NULL $($if using single$-$row INSERT statements$).$

Then, issue an UPDATE statement on Viewer to set the BestFriendID to some ViewerID, DIFFERENT from the ViewerID you are updating!

For multi$-$row INSERT statements:

You get the advantage that you can reference all the ViewerIDs automatically setting the BestFriendIDs $-$ nice!

Don't let anyone be their own best friend!

For composite entities:

The IDs that, when combined, make up the PK$,$ should NOT be set as IDENTITY fields $($as we do in non$-$composite entities$).$

When you INSERT, you will specify the IDs there.

The PK for the composite entities will be something like this for the Role table:

CONSTRAINT PK$\_$RoleID PRIMARY KEY CLUSTERED $($ ShowID ASC, ActorID ASC $)$

Don't forget to update your PK and FK names $($e$.$g$,$ PK$\_$Role, FK$\_$Role$\_$Actor, FK$\_$Role$\_$Show, etc...$).$ Too many students forget to change them when copying$/$pasting$.$

You might see some of the table names in different colors $($e$.$g$.,$ Role and Platform$).$ This is just SSMS indicating that those are SQL Server Keywords. Not an issue.

As discussed in class, DO NOT put quotes $(\text{'})$ around any number type. This includes INT, BIGINT, DECIMAL, BIT, etc... This results in "implicit conversion" work on the part of the server, and can significantly degrade performance in the real world. As discussed, points will be deducted for this.

IsMovie and InternetBased should be BIT data types.

Salary and BoxOfficeEarnings should be either INT, BIGINT, or DECIMAL$(14,2)[999,999,999,999\mathrm{.}99].$ Up to you, but definitely should not be character types. Otherwise math becomes a problem.

YearWon should NOT be a character type. Either INT or DATE.

Table names $-$ be consistent with your pattern. Note that the table names DO NOT need to start with $"$tb$\_".$ That's just an old standard.

Let the server do the work for you! When INSERTing into a table with an IDENTITY field, DO NOT override it with SET IDENTITY INSERT ON $/$ OFF. This is reserved for DBAs to override an IDENTITY field. Points will be deducted for this practice.

Make your data interesting. Set some Viewers to not have a best friend. Set some Platforms to not be internet based, and others that aren't used. Same for Genre and Director. And so on$.$