Database Schema Your database should have the following tables, where the primary key is underlined, and the referenced table of foreign keys are shown in superscript

Subject:Computer SciencePrice: Bought3

Share With

Database Schema

Your database should have the following tables, where the primary key is underlined, and the referenced table of foreign keys are shown in superscript. You must use the same names and domains as shown here and we will deduct marks if you fail to do so.

Table Schema

• Band = {bandname, startdate, members, genre}
• Musician = {msin, firstname, lastname, birthdate}
• Plays = {bandname^Band, msin^Musician, share}
• Song = {isrc, title, songyear, bandname^Band}

 Attribute domains

• bandname, firstname, lastname, genre, title - variable length character string of length 30 (use varchar, not char or nchar)
• msin - fixed length character string of length 5 (not Unicode)
• isrc - fixed length character string of length 14 (not Unicode)
• startdate, birthdate - date
• songyear, members - integer
• share- decimal (18,3)

Primary Key Constraints

• Band - bandname
• Musician - msin
• Plays - bandname, msin
• Song - isrc

Foreign Key ConstraintsPlays

• bandname references Band, deletion of referenced bands should be prevented and any changes to band names should be cascaded
• msin references Musician, deletion of referenced musicians should be cascaded and any changes to musician msins should be prevented

Song

• bandname references Band, deletion of referenced bands should be prevented and any changes to band names should be cascaded

Other ConstraintsBand

• The startdate attribute should not be null

Musician

• The lastname attribute should not be null

Song

• The songYear must be greater than or equal to the year of the band's startdate - you must create a UDF and use it in a CHECK constraint on Song
• Title and band name together constitute a candidate key

Triggers

 You should implement two triggers, both on Plays.

1. If the share for all musicians associated with a band does not sum to 1.0 an error message should be printed (using RAISERROR); the trigger should not prevent the transaction, just display an error message. This warning should be raised if the share total is incorrect (does not sum to 1.0) for any band not just those affected by the triggering transaction.
2. The members attribute of the Band table should always equal the number of musicians who are members of the band - you may implement this in multiple triggers. The(se) trigger(s) must use the contents of the inserted and deleted tables (see the constraint presentation for details) and you are not permitted to solve this by recalculating the values of all the members attributes. In other words, your trigger should only change the members attribute of the record or records affected by the update, rather than making an inefficient global recalculation.

These triggers should apply to insert, delete and update of plays records.

Stored Procedures

In addition to the above you should write the following three stored procedures, which run SQL queries, both with a variable.

The basic syntax to create a store procedure is:

CREATE PROCEDURE test
AS
BEGIN
select * from song
END

You can then run it by calling EXEC test.

You are asked to a bit more than this by using variables (which are preceded by @). I'm leaving it up to you to do some research on this - it is easy enough to find, and not overly complex.

Musicians in More than one Band but not Band X

This stored procedure must be named spMusicianMoreThanOneBand.

Return msin, last name and band names of musicians who play for at least two bands, but who do not play for one particular band- where that band (name) is determined by a variable passed to the procedure. Result should be sorted by band name, last name and msin (in ascending order).

Select Songs

This stored procedure must be named spSongsWith.

Return ISRC, title and band name for song titles that contain a string that is determined by a variable passed to the procedure. Result should be sorted by title and band name (in ascending order). Note that the name criterion, and SQL LIKE, are not case sensitive.

Bands Song Count

This stored procedure must be named spBandsSongCount.

Return band name, musician last name and the (named) count of the number of songs recorded by the band for bands where:

• The number of songs recorded by the band is greater than n, where n is the first variable to the procedure.
• The band has a musician whose last name starts or ends with the string s, where s is the second variable to the procedure - note that all a band's musician's last names should be printed even if they do not start or end with s.

Results should be printed in descending order of the song count, and in ascending band name, last name order for each count. Note that the band name and, song count will be repeated for each musician that plays for an band.

 

 

Please create constraints with SQL