More SQL

Use CommunityAssist

--functions Date time functions

Select Year(GetDate())
Select Distinct Year(DonationDate) as [Year] from Donation
Select DateDiff(dd,  '2/28/2013','1/1/2014')
Select DateAdd(dd, 30, '2/28/2013')

--aggregate functions
Select avg(DonationAmount) From Donation
Select Sum(DonationAmount) From Donation
Select Count(DonationAmount) from Donation
Select Max(DonationAmount) From Donation
Select Min(DonationAmount) From Donation

Select Year(DonationDate) as [Year], Month(DonationDate) as [Month],
 Sum(donationAmount) as Total from Donation
 Group by Year(DonationDate), Month(donationDate)
 order by Year(DonationDate)

--inner joins
Select LastName, FirstName, ContactInfo as Email
From Person
Inner Join PersonContact
on Person.PersonKey=personcontact.PersonKey
Where ContactTypeKey=6

Select LastName, FirstName, ContactInfo as Email, DonationDate
From Person
Inner Join PersonContact
on Person.PersonKey=personcontact.PersonKey
Inner Join Donation
on Person.PersonKey=Donation.PersonKey
Where ContactTypeKey=6

--insert update and delete

Insert into Person(lastName, firstName)
Values('Smith', 'Jason'),
('Doe' ,'Jamie')

Select * From Person

Insert into Person(lastName, firstName)
Values('Munz', 'Nelson')

Insert into PersonAddress(Street, Apartment, State, City, Zip, PersonKey)
Values('1000 nowhere',null,'IL', 'Springfield', '98000',ident_current('Person'))

Insert into Donation(DonationDate, DonationAmount, PersonKey, EmployeeKey)
Values(GetDate(),2.25,ident_current('Person'), 1)

Delete from person where Personkey=1

Update Person
Set FirstName='Jerry'
Where PersonKey=1063

Begin transaction

Delete from PersonContact

Select *  from PersonContact

Rollback tran

Commit Tran

Assignment six with Validation code

I am only posting the code behind for Default2.aspx.cs because that is where we made all our changes. We added some validation for the fields and a try catch for the code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        int isErrors=Validator();
        if (isErrors == 0)
        {
            try
            {
                CommunityAssistEntities cae = new CommunityAssistEntities();
                Person p = new Person();
                p.LastName = txtLastName.Text;
                p.FirstName = txtFirstName.Text;
                cae.People.Add(p);

                PersonAddress pa = new PersonAddress();
                pa.Person = p;
                pa.Street = txtStreet.Text;
                pa.City = txtCity.Text;
                pa.State = txtState.Text;
                pa.Zip = txtZip.Text;
                cae.PersonAddresses.Add(pa);

                PersonContact pc = new PersonContact();
                pc.Person = p;
                pc.ContactInfo = txtPhone.Text;
                pc.ContactTypeKey = 1;
                cae.PersonContacts.Add(pc);

                PersonContact pc2 = new PersonContact();
                pc2.Person = p;
                pc2.ContactInfo = txtEmail.Text;
                pc2.ContactTypeKey = 6;
                cae.PersonContacts.Add(pc2);

                Donation d = new Donation();
                d.Person = p;
                d.DonationDate = DateTime.Now;
                d.DonationAmount = decimal.Parse(txtDonation.Text);
                cae.Donations.Add(d);

                cae.SaveChanges();

                Response.Redirect("Default3.aspx");
            }
            catch (ArgumentNullException en)
            {
                string msg = en.Message;
                CreateAlert(msg);
            }
            catch (Exception ex)
            {

                string msg = ex.Message;
                CreateAlert(msg);
            }
        }//end if

    }//end method

    private int Validator()
    {
        int errors = 0;
         string msg;

        if (txtLastName.Text.Equals(""))
        {
            msg = "last name is required";
            CreateAlert(msg);
            errors = 1;
            
        }
        
        decimal amount=0;
        //try parse tests the text to see if it can be turned into a number
        //if no returns false, if true returns true and assigns the value to the 
        //out parameter
        bool isDonation = decimal.TryParse(txtDonation.Text, out amount);
        if (!isDonation)
        {
            msg = "You must enter a numerical donation";
            CreateAlert(msg);
            errors = 1;
        }

        if (txtState.Text.Length != 2)
        {
            msg="Please enter a 2 character state abrv.";
            CreateAlert(msg);
            errors = 1;
        }

        return errors;
    }

    private void CreateAlert(string msg)
    {
        Response.Write("<script type='text/JavaScript'>alert('" + msg + "')</script>");
    }
}

Our trigger example

Use CommunityAssist
go
Create trigger tr_FlaggedDonations
 on Donation after insert
 As
 Declare @donationAmount money
 Select @donationAmount =DonationAmount
  From Inserted

 if @donationAmount > 10000
 Begin
  if not exists
     (Select name from sys.tables
    Where name='FlaggedDonations')
  Begin
   Create table FlaggedDonations
   (
    DonationKey int, 
    DonationDate datetime, 
    DonationAmount money, 
    PersonKey int, 
    EmployeeKey int
   )
  End
  Insert into FlaggedDonations(
  DonationKey, 
  DonationDate, 
  DonationAmount, 
  PersonKey, 
  EmployeeKey)
  Select DonationKey, 
   DonationDate, 
   DonationAmount, 
   PersonKey, 
   EmployeeKey
  From Inserted
  Print 'Large Donation recieved'
 End

 Select * from FlaggedDonations 

SQL 1

use CommunityAssist

Select PersonKey, FirstName,LastName from Person
Select * From Person

Select LastName, Firstname From Person
order by Lastname Desc


Select LastName, Firstname From Person
order by Lastname Desc, FirstName DESC

Select Distinct PersonKey From Donation 
order by PersonKey

Select LastName as [Last Name], 
 FirstName as [First Name]
From Person

Select 5 * 2 + 4 /3.0

Select DonationAmount, Cast(DonationAmount * .15 as Decimal(6,2))[Maintainance], DonationAmount * .85 [To charity]
From Donation

Select * From PersonAddress

Select * From PersonAddress
where City = 'Seattle'

Select * From PersonAddress
where City = 'Kent'

Select * From Donation
Where DonationAmount >= 2500

Select * From Donation
Where DonationAmount < 2500

Select * from PersonAddress 
Where not city = 'Seattle'

Select * from PersonAddress 
Where city != 'Seattle'

Select * From Person
Where lastname like 'Mo%'

Select * From PersonAddress
where street like '%ave%'

Select * From Donation
where DonationDate between '3/1/2010' and '3/15/2010'

Select * From Donation
where DonationDate >= '3/1/2010' 
and  DonationDate <= '3/15/2010'

Select * from PersonAddress where apartment is null

Select * from PersonAddress where apartment is not null
and Apartment like '%suite%'

ADO Entities

Remember you have to add an ADO.Net Data Entities object to your web site. We called in "CommunityAssistModel". Add the tables Person, PersonAddress, PersonContact, Donation and Service.

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <ul>
        <asp:DataList ID="DataList1" runat="server">
            <ItemTemplate>
                <li><strong><%#Eval("ServiceName") %> </strong>:
                     <%#Eval("ServiceDescription") %></li>
            </ItemTemplate>
        </asp:DataList>
        </ul>
        <asp:LinkButton ID="LinkButton1" runat="server" OnClick="LinkButton1_Click">make a Donation</asp:LinkButton>
    </div>
    </form>
</body>
</html>

---

Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        CommunityAssistEntities cae = new CommunityAssistEntities();
        var serv = from s in cae.Services
                   orderby s.ServiceName
                   select new { s.ServiceName, s.ServiceDescription };
        DataList1.DataSource = serv.ToList();
        DataList1.DataBind();

    }
    protected void LinkButton1_Click(object sender, EventArgs e)
    {
        Response.Redirect("Default2.aspx");
    }
}

---

Default2.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table>
            <tr>
                <td>Enter Last Name</td>
                <td>
                    <asp:TextBox ID="txtLastName" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Enter first Name</td>
                <td>
                    <asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Enter Street Address</td>
                <td>
                    <asp:TextBox ID="txtStreet" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>City</td>
                <td>
                    <asp:TextBox ID="txtCity" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>State (2 characters)</td>
                <td>
                    <asp:TextBox ID="txtState" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Enter Zip Code</td>
                <td>
                    <asp:TextBox ID="txtZip" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Enter Home Phone</td>
                <td>
                    <asp:TextBox ID="txtPhone" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Enter Email</td>
                <td>
                    <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Enter Donation Amount</td>
                <td>
                    <asp:TextBox ID="txtDonation" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>
                    <asp:Label ID="lblMessage" runat="server" Text="Label"></asp:Label></td>
                <td>
                    <asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" /> </td>
            </tr>
        </table>
    
    </div>
    </form>
</body>
</html>

---

Default2.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        CommunityAssistEntities cae = new CommunityAssistEntities();
        Person p = new Person();
        p.LastName = txtLastName.Text;
        p.FirstName = txtFirstName.Text;
        cae.People.Add(p);

        PersonAddress pa = new PersonAddress();
        pa.Person = p;
        pa.Street = txtStreet.Text;
        pa.City = txtCity.Text;
        pa.State = txtState.Text;
        pa.Zip = txtZip.Text;
        cae.PersonAddresses.Add(pa);

        PersonContact pc = new PersonContact();
        pc.Person = p;
        pc.ContactInfo = txtPhone.Text;
        pc.ContactTypeKey = 1;
        cae.PersonContacts.Add(pc);

        PersonContact pc2 = new PersonContact();
        pc2.Person = p;
        pc2.ContactInfo = txtEmail.Text;
        pc2.ContactTypeKey = 6;
        cae.PersonContacts.Add(pc2);

        Donation d = new Donation();
        d.Person = p;
        d.DonationDate = DateTime.Now;
        d.DonationAmount = decimal.Parse(txtDonation.Text);
        cae.Donations.Add(d);

        cae.SaveChanges();

        Response.Redirect("Default3.aspx");

    }
}

Stored Procedure

--stored Procedures
/*
create a proc that adds a new donor and donation
Get all the values for the tables below
Insert into person
Insert into PersonAddress
Insert into Personcontact
Insert into Donation
all or none
*/
use CommunityAssist
Go

Alter Procedure usp_NewDonor
--get parameters
@lastName nvarchar(255),
@firstName nvarchar(255),
@street  nvarchar(255),
@apartment nvarchar(255) = null,
@city nvarchar(255) = 'Seattle',
@state nvarchar(2)='WA',
@zip nvarchar(10),
@homePhone nvarchar(255),
@email nvarchar(255),
@donationAmount money
As
--declare class level variable
Declare @personKey int
Begin Transaction
Begin Try
--check to see if person exists
if not exists
 (Select Lastname, firstName, ContactInfo
  From Person p
  inner Join PersonContact pa
  on p. PersonKey = pa.PersonKey
  where LastName=@LastName
  And Firstname=@firstname
  and Contactinfo = @Email
  and contactTypeKey=6)
Begin --begin if they don't exist
Insert into Person (Lastname, firstName)
Values (@lastName, @firstName)

--personkey is set to new identity
Set @PersonKey=ident_current('Person')

Insert into PersonAddress(Street, Apartment, [State], City, Zip, PersonKey)
Values (@street, @Apartment, @State, @City, @Zip,@personKey )

Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values(@homePhone, @PersonKey, 1),
(@Email, @personKey, 6)
End -- end if true
Else --if they do exist
Begin
--get the existing person's key
Select @personkey=p.personkey 
from Person p
inner join PersonContact pc
on p. PersonKey=pc.Personkey
Where LastName=@lastName
And FirstName=@FirstName
And Contactinfo = @Email
And ContactTypeKey = 6

End --end of they do exist
--no matter what insert donation

Insert into Donation(DonationDate, DonationAmount, PersonKey, EmployeeKey)
Values (GetDate(), @donationAmount, @personKey, null)
Commit Tran
End try
Begin Catch
print 'The transaction failed ' + error_message()
Rollback Tran
End catch

Go

Exec usp_NewDonor
@lastName ='Hathawat',
@firstName ='Ann',
@street ='111 somewhere Street',
@Apartment='101',
@City='Bellevue',
@zip ='98556',
@homePhone='2065555753',
@email ='hathawat@gmail.com',
@donationAmount =3000

Create unique index ix_email on PersonContact(contactInfo)
where ContactTypeKey=6

Select * From Person
Select * from PersonAddress
Select * From PersonContact
Select * From Donation

In Class ADO Example

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table>
        <tr>
            <td>User Name</td>
            <td>
                <asp:TextBox ID="txtUser" runat="server"></asp:TextBox></td>
        </tr>
        <tr>
            <td>Password</td>
            <td>
                <asp:TextBox ID="txtPassword" runat="server" TextMode="Password"></asp:TextBox></td>
        </tr>
        <tr>
            <td>
                <asp:Label ID="lblMsg" runat="server" ></asp:Label></td>
            <td>
                <asp:Button ID="btnLogin" runat="server" Text="Login" OnClick="btnLogin_Click" /></td>
        </tr>
    </table>
    </div>
    </form>
</body>
</html>

---

Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnLogin_Click(object sender, EventArgs e)
    {
        DonorLoginClass dl = new DonorLoginClass();
        int person = dl.Login(txtUser.Text, txtPassword.Text);
        if (person != 0)
        {
            Session["person"] = person;
            Response.Redirect("Default2.aspx");
        }
        else
        {
            lblMsg.Text = "invalid login";
        }
    }
}

---

Default2.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="lblName" runat="server" Text="Label"></asp:Label>
        <asp:GridView ID="GridView1" runat="server"></asp:GridView>
    </div>
    </form>
</body>
</html>

---

DonorLoginClass.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// 

/// Summary description for DonorLoginClass
/// 
public class DonorLoginClass
{
    private SqlConnection connect;

 public DonorLoginClass()
 {
        string connectionString = @"Data Source=localhost;"
            + "initial catalog=CommunityAssist;"
            + "user=DonorsLogin;password=P@ssw0rd1";
        connect = new SqlConnection(connectionString);
 }

    public int Login(string userName, string passWord)
    {
        int pKey = 0;
        string sql = "Select PersonKey, LastName, DonorPassword From DonorLogin";
            //+ "Where LastName=@LastName and DonorPassword=@password";
        SqlCommand cmd = new SqlCommand(sql, connect);
       // cmd.Parameters.AddWithValue("@LastName", userName);

        SqlDataReader reader = null;
        connect.Open();
        reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            if (reader["LastName"].ToString().Equals(userName)
                && reader["DonorPassword"].ToString().Equals(passWord))
            {
                pKey = int.Parse(reader["PersonKey"].ToString());
                break;
            }
        }
        reader.Close();
        connect.Close();
        return pKey;
    }
}

---

GetDonor.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// 

/// Summary description for GetDonor
/// 
public class GetDonor
{
    SqlConnection connect;
    int personKey;
 public GetDonor(int pKey)
 {
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
        builder.DataSource = "localhost";
        builder.InitialCatalog = "CommunityAssist";
        builder.UserID = "RegisteredDonorsLogin";
        builder.Password = "P@ssw0rd1";
        connect = new SqlConnection(builder.ToString());
        personKey = pKey;
 }

    public string GetDonorName()
    {
        string info = null;

        string sql = "Select LastName, FirstName From Person " +
                        "Where PersonKey=@PersonKey";
        SqlCommand cmd = new SqlCommand(sql, connect);
        cmd.Parameters.AddWithValue("@PersonKey", personKey);

        SqlDataReader reader = null;
        connect.Open();
        reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            info = reader["FirstName"].ToString() + "  " + reader["LastName"].ToString();
        }
        reader.Close();
        connect.Close();

        return info;
    }

    public DataSet GetDonations()
    {
        DataSet ds = new DataSet();
        string sql = "Select DonationDate, DonationAmount From Donation " +
            "Where PersonKey=@PersonKey";
        SqlCommand cmd = new SqlCommand(sql, connect);
        cmd.Parameters.AddWithValue("@PersonKey", personKey);
        SqlDataReader reader = null;

        connect.Open();
        reader = cmd.ExecuteReader();
        ds.Load(reader, LoadOption.OverwriteChanges, "Donation");
        reader.Close();
        connect.Close();
        return ds;
    }

    
}

Functions, Simple Procedures

--Functions

Use CommunityAssist
Go
Create function fx_Cubed
(@number int)
Returns int
As
Begin
Return @number * @number * @number
End

Select Personkey, dbo.fx_Cubed(PersonKey) as cubed 
From Person

--For every donation 77% goes to charity
--33% goes for maintainance 
Go
Create Function fx_ToCharity
(@donation money)
Returns money
As
Begin
Return @donation *.77
End

go
Create Function fx_Maintainance
(@donation money)
returns money
as
Begin
Return @donation *.33
End
Go

Select donationamount, 
  dbo.fx_toCharity(donationAmount) as Charity,
  dbo.fx_Maintainance(DonationAmount) as Maintainance
From Donation

Select year(DonationDate) as [Year],
sum(dbo.fx_ToCharity(donationamount)) as [Total charity],
 sum(dbo.fx_Maintainance(donationAmount)) as [Total Overhead]
 From Donation
 Group by year(donationDate)
Go

Alter Function fx_MoreAdvancedToCharity
(@donation money, @Percent decimal(6,2))
Returns money
As
Begin
if(@Percent >1.00)
Begin
Set @Percent = @percent/100
End
return @donation * @Percent
End

Select donationAmount, 
dbo.fx_MoreAdvancedToCharity(donationAmount, 75) as [to Charity], 
dbo.fx_MoreAdvancedTocharity(donationAmount, .25) as Overhead
From Donation

Go
Alter procedure usp_ReturnDonorInfo
@Lastname nvarchar(255) 
as
Select lastname, Firstname, Street, City, State, Zip, contactInfo
From Person p
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey
inner join PersonContact pc
on p.PersonKey=pc.PersonKey
Where Lastname=@lastName

Execute dbo.usp_ReturnDonorInfo 
@lastname='Manning'

Views and Indexes

 --Views

 use communityAssist
 Go
 Alter View vw_donorInfo
 AS
 Select Lastname as [Last Name],
 FirstName as [First Name],
 ContactInfo as Email
 From Person p
 inner Join PersonContact pc
 on p.PersonKey=pc.PersonKey
 Where ContactTypeKey=6
 Go
 --to change the view you either drop and recreate
 Drop View vw_donorInfo

 --or you can make changes with ALTER


 Update vw_donorInfo
 Set [last Name] = 'manning'
 Where Email='lmann@mannco.com'

 Select * from vw_donorInfo
 order by [Last Name]

Select * from vw_donorInfo
Where [last name] like 'C%' 

Create View vw_person
As
Select PersonKey,FirstName,Lastname
From Person

Select * from vw_person
Where 

Update vw_Person set Firstname='Jason' where Personkey=1


Create nonclustered index ix_lastname on Person(LastName)
Create nonclustered index ix_last on Person(LastName)

--query which forces the use of the ix_lastname 
Select Lastname as [Last Name],
 FirstName as [First Name],
 ContactInfo as Email
 From Person p WITH ( INDEX (ix_lastname) )
 inner Join PersonContact pc
 on p.PersonKey=pc.PersonKey
 Where ContactTypeKey=6

 --unique and filtered index
 Create unique index ix_email on PersonContact(contactinfo)
 Where ContactTypeKey=6

 --drop an index
 Drop index ix_email on PersonContact

 --composite index (two or more columns
 Create index ix_Address on PersonAddress(State, zip)

 --primary key's usually have a clustered index by default
 --so to add a clustered index you need to drop the key
 Alter table PersonB
 Drop Constraint [PK__PersonB__5F59DF1842B1C384]

 
 --now add a key
 Create clustered Index ix_Personb on PersonB(PersonKey)

--this would create a primary key without a clustered index
Alter table PersonB
Add Constraint PK_Personb Primary Key nonclustered(PersonKey) 

 Select * from PersonB

 Drop index ix_PersonB on PersonB

  Create clustered Index ix_Personb on PersonB(LastName)

Movie ERD

Here is the Movie ERD

Creating and altering tables

Use CommunityAssist

Insert into Person(lastName, firstname)
Values ('Isabella', 'Queen')

insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values('queen@gmail.com', Ident_Current('Person'), 6)

Select * from Person

Select * From PersonContact

Use Master;

Create Database Music

Use Music

Create table Album
(
 AlbumKey int identity(1,1) primary key,
 AlbumTitle nvarchar(255) not null,
 AlbumDate Date,
 AlbumPrice Decimal(5,2) not null default 1, 
 AlbumArt varbinary(max) 
)

Create Table Track
(
 TrackKey int identity not null,
 TrackTitle nvarchar(255) not null,
 TrackLength decimal(5,2),
 AlbumKey int not null,
 Constraint PK_Track Primary key(TrackKey),
 Constraint FK_Album Foreign Key (AlbumKey)
  references Album(AlbumKey)
)

Create table Artist
(
 Artistkey int identity(1,1) not null,
 Artistname nvarchar(255) not null,
 ArtistInfo xml 
)

Alter table Artist
Add constraint PK_Artist Primary key(ArtistKey)

Alter Table Album
Drop constraint [PK__Album__554E540739505B7C]

Drop Table Album

Create table TrackArtist
(
 TrackArtistKey int identity(1,1) primary key,
 ArtistKey int Foreign Key references Artist(ArtistKey),
 Trackkey int not null
)

Alter table TrackArtist
Add Constraint FK_Track Foreign key (TrackKey)
 References Track(TrackKey)

Alter table TrackArtist
Add TrackDescription xml

Alter Table TrackArtist
Drop Column TrackDesription

Alter Table Album
Add Constraint unique_AlbumTitle unique(AlbumTitle)

Alter Table Album
Add Constraint album_PriceRange Check (AlbumPrice between 1 and 500)

Alter Table Section
Add Constraint chk_Quarter 
Check (Qtr in ('Fall', 'Winter', 'Spring', 'Summer'))