USE pubs

--To create NrmlExp1, run the view at the bottom of this script
/*
The purpose of this section is to go beyond the theoretical or introductory and 
learn some practical skills for analyzing relations and normalizing
relations to at least 3NF. 

You won't always start from scratch with a database. In fact, I would argue that
most of the time, you will either inherit data (usually not relational) or a 
relational database that may or may not be normalized.

CHOICES grant, 3-year, $1.3 million 

What to do?


*/
SELECT * FROM NrmlExp1

SELECT stor_name, ord_date, qty, title, pub_name, type, state, authors 
FROM NrmlExp1

/*
pp. 97
Is NrmlExp a relation? Clearly it is a table, but is it a relation?
  Codd's characteristics of a relation
  1. rows contain data about one entity
  2. columns contain data about attributes of the entities
  3. all entries in a column are of the same kind
  4. each column has a unique name
  5. cells of the table hold a single value
  6. the order of the columns is unimportant
  7. the order of the rows is unimportant
  8. no two rows may be identical

BLOB
dtMyDataTable.Rows[0][1].ToString();
SELECT TOP 10 * FROM ...

2nf means that all non-key attributes are 
dependent upon the entire
key and not part of the key.

3nf there are no transitive dependencies.
A non-key attribute determines another non-key attribute.


*/

--How to deal with number 5? Remove multiple entries or multi-valued attributes pp. 108
--You could use SQL Server string operations to remove them or write a utility that parses
--the string. Assume we took care of it and the relation now looks like:

SELECT stor_name, ord_date, qty, 
title, pub_name, type, state, author1, author2, author3 
FROM NrmlExp1

--.8 Are the rows unique? How would you find out?
SELECT stor_name, ord_date, qty, title, pub_name, type, state, author1, author2, author3
FROM NrmlExp1

--We could count the DISTINCT rows
--First how many rows in the table?
SELECT COUNT(*)NumberOfRows FROM NrmlExp1

--SELECT DISTINCT * FROM NrmlExp1

SELECT COUNT(*) NumberOfRows
FROM (SELECT DISTINCT * FROM NrmlExp1) AS DistinctRows


--Or we could use GROUP BY HAVING to look for duplicate combinations
--If you look in the SQL Server help files you will find:

/* The GROUP BY clause restricts the rows 
of the result set; there is only one row for each 
distinct value in the grouping column or columns. 
*/
SELECT DISTINCT * FROM NrmlExp1

SELECT COUNT(*) AS Rowcnt, stor_name, ord_date, qty, title, pub_name, type, state, author1, author2, author3
FROM NrmlExp1
GROUP BY stor_name, ord_date, qty, title, pub_name, type, state, author1, author2, author3
HAVING COUNT(*) > 1

--How to remove the duplicates? 
--DROP TABLE NrmlExp2
SELECT stor_name, ord_date, qty, title, pub_name, type, state, author1, author2, author3 
FROM NrmlExp1
GROUP BY stor_name, ord_date, qty, title, pub_name, type, state, author1, author2, author3

--Add an INTO statement to create a new table
--DROP TABLE NrmlExp2
SELECT stor_name, ord_date, qty, title, pub_name, type, state, author1, author2, author3 
INTO NrmlExp2  --Create a materialized view
FROM NrmlExp1
GROUP BY stor_name, ord_date, qty, title, pub_name, type, state, author1, author2, author3

CREATE VIEW vwNrmlExp2 AS
SELECT stor_name, ord_date, qty, title, pub_name, type, state, author1, author2, author3 
FROM NrmlExp1
GROUP BY stor_name, ord_date, qty, title, pub_name, type, state, author1, author2, author3

SELECT * FROM NrmlExp2
SELECT * FROM vwNrmlExp2

--OR...

CREATE TABLE NrmlExp2
(stor_name NVARCHAR(100) NULL, 
 ord_date DATETIME NULL, 
 qty INT NULL, 
 title NVARCHAR(100) NULL, 
 pub_name NVARCHAR(100) NULL, 
 type NVARCHAR(100) NULL, 
 state CHAR(2) NULL, 
 author1 NVARCHAR(100) NULL, 
 author2 NVARCHAR(100) NULL, 
 author3 NVARCHAR(100) NULL
)

INSERT INTO NrmlExp2
   SELECT stor_name, ord_date, qty, title, pub_name, type, state, author1, author2, author3 
   FROM NrmlExp1
   GROUP BY stor_name, ord_date, qty, title, pub_name, type, state, author1, author2, author3

SELECT stor_name, ord_date, qty, title, pub_name, type, state, author1, author2, author3 
FROM NrmlExp2

--Look for determinates, candidate keys 
--Note that sample data may or may not indicate a "true" determinate. p.103 (By the way)
--Specifically, we want to find the minimal 
--set of attributes that
--uniquely identifies a row.
--Are there any unique columns?
--How would you check?

SELECT COUNT(DISTINCT stor_name) stor_name, COUNT(DISTINCT ord_date) ord_date, 
       COUNT(DISTINCT qty) qtyCnt, COUNT(DISTINCT title) title, 
       COUNT(DISTINCT pub_name) pub_name, COUNT(DISTINCT type) type, 
       COUNT(DISTINCT state) state, COUNT(DISTINCT author1) author1, 
       COUNT(DISTINCT author2) author2, COUNT(DISTINCT author3) author3 
FROM NrmlExp2

--None of these counts equal 21 
--so there is not a single column candidate key

/*
Keys pp. 104
Are there any attribute combinations that uniquely identify the relation? 
That is, are thare any composite candidate keys?
One way to look for composite candidate keys is to GROUP BY the
combinations and determine if there is only one row for each
combination.

SELECT COUNT(*), attrib1, attrib2
FROM table
GROUP BY attrib1, attrib2
HAVING COUNT(*) > 1

If such as query as above returns no rows (empty set), there
are no duplicates of the combination and at least for the sample
data, that combination is a determinate.

*/

SELECT stor_name, ord_date, qty, title, pub_name, type, state, author1, author2, author3 
FROM NrmlExp2

--(stor_name,Order date)?
SELECT COUNT(*) AS GrpCnt, stor_name, ord_date--, qty, title, pub_name, type, state, author1, author2, author3 
FROM NrmlExp2
GROUP BY stor_name, ord_date
HAVING COUNT(*) > 1

--(stor_name, ord_date, title)? 
SELECT COUNT(*) AS GrpCnt, stor_name, ord_date, title--qty, pub_name, type, state, author1, author2, author3 
FROM NrmlExp2
GROUP BY stor_name, ord_date, title
HAVING COUNT(*) > 1

SELECT COUNT(*) AS GrpCnt, stor_name, title--qty, pub_name, type, state, author1, author2, author3 
FROM NrmlExp2
GROUP BY stor_name, ord_date, title
HAVING COUNT(*) > 1


--Ahhh yes. The combination of (stor_name, ord_date, title) 
--is unique for every row in the relation
--and will likely be the minimal set of 
--attributes that uniquely identify a row

--Do we make the composite (stor_name, ord_date, title) 
--the primary key? pp. 105
--Or do we use a Surrogate key? pp. 105
--a relational database is a self-describing collection
--of integrated records. Integrated not through pointers,
--but through the data themselves.

--How would we create a surrogate single attribute primary key for the entire table?
--Add an identity column to the table

INSERT INTO tblNewTable
  SELECT attribute... (SELECT MAX(pk) + 1 
                       FROM tblUser AS InnerTable
                       WHERE tbUser.PK=InnerTable.PK) AS PK
  FROM tblUser

sp_help NrmlExp2

--Build surrogate key for legacy data
ALTER TABLE NrmlExp2
ADD TranID INT IDENTITY(1,1) PRIMARY KEY

SELECT TranID, stor_name, ord_date, qty, title, pub_name, type, state, author1, author2, author3 
FROM NrmlExp2

--Now we can uniquely identify any row by the surrogate key.
SELECT TranID, stor_name, ord_date, qty, title, pub_name, type, state, author1, author2, author3 
FROM NrmlExp2
WHERE TranID = 7

SELECT TranID, stor_name, ord_date, qty, title, pub_name, type, state, author1, author2, author3 
FROM NrmlExp2
WHERE stor_name = 'Doc-U-Mat: Quality Laundry and Books' AND
      ord_date = '1993-05-29 00:00:00.000' AND
      title = 'Prolonged Data Deprivation: Four Case Studies'
      


/*
However, if the composite key (stor_name, ord_date, title) really is
a determinate and therefore a candidate key, we would probably want to
create a UNIQUE constraint on the combination of attributes in order to
maintain the integrity of the determinancy.
For example, we may not want to allow the following transaction:
*/

INSERT INTO NrmlExp2 (stor_name, ord_date, qty, title, pub_name, type, state, author1, author2, author3)
  SELECT stor_name, ord_date, qty, title, pub_name, type, state, author1, author2, author3 
  FROM NrmlExp2
  WHERE TranID = 7

SELECT TranID, stor_name, ord_date, qty, title, pub_name, type, state, author1, author2, author3 
FROM NrmlExp2
WHERE TranID IN(7, 22)

--It appears we have a duplicate transaction despite the fact that each row is unique. (rule 8)

DELETE NrmlExp2 WHERE TranID = 22

ALTER TABLE NrmlExp2
ADD CONSTRAINT stor_name_ord_date_title_unqiue 
   UNIQUE(stor_name, ord_date, title)

ALTER TABLE NrmlExp2
DROP CONSTRAINT stor_name_ord_date_title_unqiue
sp_help NrmlExp2


/*
Finding functional dependencies pp. 101

pp. 78 "No single skill is more important for designing 
databases than the ability to identify functional 
dependencies." (10th edition)

Now he simply states: "Functional dependencies are the heart of 
database design process, and it is vital for you to understand
them." (11th edition)

We can check the functional relationships (at least for the sample data)
 {stor_name,  ord_date, title} --> {qty, pub_name, type, state}

with a GROUP BY that concatenates the 
composite key and COUNTS the 
DISTINCT values of the concatenated 
determined attribute(s).

The basic form is:
SELECT COUNT(DISTINCT dependent), determinant
FROM table(s)
GROUP BY determinant
HAVING COUNT(DISTINCT dependent) > 1

In the cases where either the determinant 
or the dependent attribute
is a composite, concatenate attributes

For example:

SELECT COUNT(DISTINCT dependent), determinant1 + determinant2
FROM table(s)
GROUP BY determinant1 + determinant2
HAVING COUNT(DISTINCT dependent) > 1
*/

--Returns no rows so for this sample of data, 
--the functional dependency holds
SELECT COUNT(DISTINCT CAST(qty AS NVARCHAR) + pub_name + type + state) AS GroupCnt, 
    stor_name + CAST(ord_date AS NVARCHAR) + title  AS Determinant
FROM NrmlExp2
GROUP BY stor_name + CAST(ord_date AS NVARCHAR) + title
HAVING COUNT(*) > 1

/*
Again, just because we are using a surrogate key 
doesn't mean we can
ignore the functional dependency 

                  determinant  -->  dependent
 {stor_name,  ord_date, title} --> {qty, pub_name, type, state}

Same solution as before: UNIQUE constraint.
*/
SELECT * FROM NrmlExp2


/*
Which other functional dependencies can we identify/confirm?
Looking at the values of the data, it appears that:
title --> type
title --> {author1, author2, author3}
stor_name --> state OR pub_name --> state
*/

-- title --> type
SELECT COUNT(DISTINCT type) AS GrpCnt, title
FROM NrmlExp2
GROUP BY title
HAVING COUNT(DISTINCT type) > 1

--We can visually inspect the rows with DISTINCT and ORDER BY
SELECT DISTINCT title, type
FROM NrmlExp2
ORDER BY title, type

--In all cases, a value for title returns only one value for type
--for example,
SELECT title, type
FROM NrmlExp2
WHERE title = 'Is Anger the Enemy?'
--In all four cases, the type returned is 'psychology'


--  title --> {author1, author2, author3}
SELECT COUNT(DISTINCT author1 + ISNULL(author2, '') + ISNULL(author3, '')) AS GrpCnt, title 
FROM NrmlExp2
GROUP BY title
HAVING COUNT(DISTINCT author1 + ISNULL(author2, '') + ISNULL(author3, '')) > 1

SELECT author1 + ISNULL(author2, '') + ISNULL(author3, '') AS AuthorDependent
FROM NrmlExp2

SELECT * FROM NrmlExp2
-- stor_name --> state OR pub_name --> state
SELECT COUNT(DISTINCT state) AS GrpCnt, stor_name
FROM NrmlExp2
GROUP BY stor_name
HAVING COUNT(DISTINCT state) > 1
--Visually we can see there are many values of state for each store

SELECT DISTINCT stor_name, state
FROM NrmlExp2
ORDER BY stor_name

--For example, there are three different states associated with:
SELECT DISTINCT stor_name, state
FROM NrmlExp2
WHERE stor_name = 'Fricative Bookshop'

/*
To say that stor_name --> state is a functional 
dependency is like 
saying 
2 + 2 = 4 or {2+2} --> 4
*/

--It seems that state is determined by pub_name, not
--stor_name.
SELECT COUNT(DISTINCT state) AS GrpCnt, pub_name
FROM NrmlExp2
GROUP BY pub_name
HAVING COUNT(DISTINCT state) > 1

--Won't work without DISTINCT key word
SELECT COUNT(state) AS GrpCnt, pub_name
FROM NrmlExp2
GROUP BY pub_name
HAVING COUNT( state) > 1

/*
Looks like pub_name --> state holds for the sample data

Is there yet possibly one more functional dependency?
  title  --> pub_name
  A title has only one publisher?
*/

SELECT COUNT(DISTINCT pub_name) AS GrpCnt, title
FROM NrmlExp2
GROUP BY title
HAVING COUNT(DISTINCT pub_name) > 1

SELECT DISTINCT title, pub_name
FROM NrmlExp2
ORDER BY title

SELECT title, pub_name
FROM NrmlExp2
WHERE title = 'Is Anger the Enemy?'
--yep, a title has only one publisher. {New Moon Books}


/*
Given the functional dependencies inferred from the sample data
{stor_name,  ord_date, title} --> {qty, pub_name, type, state}
title --> type
title --> {author1, author2, author3}
pub_name --> state
title  --> pub_name

What are the implications for this design?
 1. data redundancy
 2. modification anomalies
    delete anomaly
    insertion anomaly
    update anomaly
*/

/*
Deletion anomaly.
  what is the effect of deleting the row:
*/
SELECT TranID, stor_name, ord_date, qty, title, pub_name, type, state, author1, author2, author3 
FROM NrmlExp2
WHERE TranID = 7

/*
In the sample data anyway, 
row 7 is the only occurence of the author 'White'.
Therefore we risk losing information about the 
author 'White'.

Insert anomaly
   What is the effect of adding a new store:
Assume we did create the composite index for 
{stor_name,  ord_date, title} the composite 
candidate key and we enforced the constraint that no
attributes can be NULL.

*/

ALTER TABLE NrmlExp2
ALTER COLUMN stor_name NVARCHAR(100) NOT  NULL

ALTER TABLE NrmlExp2
ALTER COLUMN ord_date DATETIME NULL

ALTER TABLE NrmlExp2
ALTER COLUMN title NVARCHAR(100) NULL

ALTER TABLE NrmlExp2
ALTER COLUMN qty INT NULL

ALTER TABLE NrmlExp2
ALTER COLUMN Type NVARCHAR(100) NULL


ALTER TABLE NrmlExp2
ADD CONSTRAINT constr_UniqueTrans UNIQUE(stor_name,  ord_date, title)

ALTER TABLE NrmlExp2
DROP CONSTRAINT constr_UniqueTrans

SELECT * FROM NrmlExp2

INSERT INTO NrmlExp2 (Stor_Name) 
VALUES ('Books-a-Zillion')

delete NrmlExp2 where TranID = 27

/*
If we enforce the uniqueness of the candidate key, 
we won't be able to insert
this row because we haven't provided a 
value for ord_date or title. In other 
words, with the current structure, 
we can't insert a store until it has placed
a sale. This would hold true for 
attempting to insert a new title. We couldn't
do so until it was sold by a store.

update anomaly
What would be the result of the following UPDATE query?
*/
SELECT * FROM NrmlExp2

Update NrmlExp2 
SET pub_name = 'New Moon Books, Inc'
WHERE TranID = 2
/*
We would have the old value in records (7,9,10,11,13,15,21)
This problem can also lead to ambiguities in subsequent updates.
*/
SELECT TranID, stor_name, ord_date, qty, title, pub_name, type, state, author1, author2, author3 
FROM NrmlExp2
WHERE pub_name = 'New Moon Books'


SELECT TranID, stor_name, ord_date, qty, title, pub_name, type, state, author1, author2, author3 
FROM NrmlExp2

Update NrmlExp2 
SET pub_name = 'New Moon Books'
WHERE TranID = 2

/*
Process for putting a relation into BCNF Figure 3-13, pp. 110

1. Identify every functional dependency
2. Identify every candidate key
3. If there is a functional dependency that has a 
   determinant that is not a candidate key:
   A. Move the columns of that functional dependency to a new relation
   B. Make the determinant of that functional dependency the
      primary key of the new relation
   C. Leave a copy of the determinant as a foreign key in the 
      original relation
   D. Create a referential integrity constraint between the 
      original relation and the new relation
4. Repeat step 3 until every determinant of every relation is a 
   candidate key
*/
/*
We have done steps 1 and 2

Our candidate key and the determined attributes.

{stor_name,  ord_date, title} --> {qty, pub_name, type, state}

we identified: 

title --> type
title --> {author1, author2, author3}
title --> pub_name --> state

This leaves only qty to remain with the candidate key.
And because title isn't a very good choice for key,
we are going to move title out and create a new key titleID.

Step 3A.
Retrieve distinct rows for attributes involved in dependencies

*/
SELECT DISTINCT title, pub_name, type, state, author1, author2, author3
FROM NrmlExp2

--Create a new table
--DROP TABLE nrmTitle
SELECT DISTINCT title, pub_name, type, state, author1, author2, author3
INTO NrmTitle
FROM NrmlExp2

SELECT title, pub_name, type, state, author1, author2, author3 
FROM NrmTitle

--3B.
--Add a surrogate primary key to our new relation
ALTER TABLE NrmTitle
ADD TitleID INT IDENTITY(1,1) PRIMARY KEY

--Notice that the identity automatically creates a sequence of numbers.

SELECT titleID, title, pub_name, type, state, author1, author2, author3  
FROM NrmTitle

--3C.
--We need to create a copy of TitleID in NrmlExp2 as a foreign key
ALTER TABLE NrmlExp2
ADD TitleID INT NULL

--Why did I have to specify NULL?

SELECT TranID, stor_name, ord_date, qty, titleID 
FROM NrmlExp2

/*
Now we have to insert a value for the fk
The generic struction is

UPDATE newTable/oldtable
SET ForiegnKey = PrimaryKey
FROM newTable, oldTable 
WHERE newTable.Identifier=oldTable.Identifier

This query is based on the idea that you can create a
relation from a join and then assign the value of one 
column to the value of another column, even if that 
column is in a different table.

*/


SELECT NrmTitle.title as 'PKtitle', NrmlExp2.title as 'Fktitle', 
       NrmTitle.TitleID as 'PkTitleID',  NrmlExp2.TitleID as 'FkTitleID',
       'NrmTitle.TitleID=NrmlExp2.TitleID' as Assignmentstatement
FROM NrmlExp2, NrmTitle 
WHERE NrmTitle.title=NrmlExp2.title

UPDATE NrmlExp2
SET NrmlExp2.TitleID = NrmTitle.TitleID
FROM NrmlExp2, NrmTitle 
WHERE NrmTitle.title=NrmlExp2.title

SELECT TranID, stor_name, ord_date, qty, titleID  
FROM NrmlExp2
--3D. Create a referential integrity constraint

ALTER TABLE NrmlExp2
ADD CONSTRAINT fk_Title 
FOREIGN KEY (TitleID) 
REFERENCES NrmTitle(TitleID)

--Test by attempting to update a fk value to an invalid pk value
--There is no NrmTitle.TitleID with value 500.
UPDATE NrmlExp2 SET TitleID = 500 WHERE TranID = 1

--Cleanup. Remove copied columns from NrmlExp2
--Very very dangerous operation!
ALTER TABLE NrmlExp2
DROP CONSTRAINT constr_UniqueTrans

ALTER TABLE NrmlExp2
DROP COLUMN title, pub_name, type, state, author1, author2, author3

SELECT * FROM NrmlExp2
/*
4.
Are there any determinants in NrmlExp2 that are not candidate keys?
No.
What about in NrmTitle?
2. What is the candidate key(s)
 Title
*/

SELECT titleID, title, pub_name, type, state, author1, author2, author3
FROM NrmTitle
/*
We have
 title --> pub_name
 pub_name --> state
3.A
Move them out!
*/
--Three rows
SELECT DISTINCT pub_name, state
FROM NrmTitle
--Insert them into a new table
SELECT DISTINCT pub_name, state
INTO NrmPublisher
FROM NrmTitle

SELECT * FROM NrmPublisher

--3B.
--Add a surrogate primary key to our new relation
ALTER TABLE NrmPublisher
ADD PublisherID INT IDENTITY(1,1) PRIMARY KEY

SELECT PublisherID, pub_name, state 
FROM NrmPublisher

--3C.
--We need to create a copy of PublisherID in NrmTitle as a foreign key
ALTER TABLE NrmTitle
ADD PublisherID INT NULL

SELECT titleID, title, pub_name, type, state, author1, author2, author3, PublisherID 
FROM NrmTitle

UPDATE NrmTitle
SET NrmTitle.PublisherID = NrmPublisher.PublisherID
FROM NrmPublisher, NrmTitle 
WHERE NrmTitle.pub_name=NrmPublisher.pub_name

SELECT titleID, title, pub_name, type, state, author1, author2, author3, PublisherID 
FROM NrmTitle
--3D. Create a referential integrity constraint

ALTER TABLE NrmTitle
ADD CONSTRAINT fk_PublisherID 
FOREIGN KEY (PublisherID) 
REFERENCES NrmPublisher(PublisherID)

--Test by attempting to update a fk value to an invalid pk value
--There is no NrmPublisher.PublisherID with value 500.
UPDATE NrmTitle SET PublisherID = 500 WHERE TitleID = 1

SELECT titleID, title, pub_name, type, state, author1, author2, author3, PublisherID 
FROM NrmTitle


--Cleanup. Remove copied columns from NrmTitle
--Very very dangerous operation!
ALTER TABLE NrmTitle
DROP COLUMN pub_name, state

SELECT *--TitleID, PublisherID, title, pub_name, type, state, author1, author2, author3 
FROM NrmTitle
/*
4.
Are there any determinants in NrmPublisher that are not candidate keys?
No.
*/

/*
We still have to deal with the pesky little problem of the repeating group, 
that is the 1NF violation.
We want to create a new relation that contains the author(s) for a title.
In this way, we will push the redundancy from the columns into the rows
of a new relation creating a 1:M relationship. In this case, the new relation
will be the M relation. In the two previous operations we created a new
1 relation.
*/

SELECT TitleID, PublisherID, title, type, author1, author2, author3 
FROM NrmTitle

/*
Probably the most straightforward way is to create three 
separate
queries for each column and then UNION them together.
The order of the authors is the order of the authors in 
the publication.
That is, the first name is the first author (leading author).
The uncorrelated subquery TitleAuthor puts the columns into rows.
The alias "Author" creates a common column name.
The WHERE clause in the outer query eliminates rows
where the title didn't have a second or third author.
*/
DROP TABLE nrmAuthorship
SELECT title, Author, AuthorOrder
INTO nrmAuthorship
FROM (
     SELECT title, author1 AS Author, 1 AS AuthorOrder
     FROM NrmTitle
   UNION
     SELECT title, author2 AS Author, 2 AS AuthorOrder
     FROM NrmTitle
   UNION
     SELECT title, author3 AS Author, 3 AS AuthorOrder
     FROM NrmTitle) AS TitleAuthor
WHERE Author IS NOT NULL
ORDER BY Title, AuthorOrder

--Now write this to a new table
SELECT title, Author, AuthorOrder
INTO NrmTitleAuthor
FROM (
     SELECT title, author1 AS Author, 1 AS AuthorOrder
     FROM NrmTitle
   UNION
     SELECT title, author2 AS Author, 2 AS AuthorOrder
     FROM NrmTitle
   UNION
     SELECT title, author3 AS Author, 3 AS AuthorOrder
     FROM NrmTitle) AS TitleAuthor
WHERE Author IS NOT NULL
ORDER BY Title, AuthorOrder

SELECT *
FROM nrmAuthorship

--Add a surrogate primary key
ALTER TABLE NrmTitleAuthor
ADD TitleAuthorID INT IDENTITY(1,1) PRIMARY KEY

SELECT *
FROM NrmTitleAuthor

--Create a foreign key to be linked to NrmTitle

ALTER TABLE NrmTitleAuthor
ADD TitleID INT NULL

SELECT *
FROM NrmTitleAuthor

--Now insert the fk values based on the title.
UPDATE NrmTitleAuthor
SET NrmTitleAuthor.TitleID = NrmTitle.TitleID
FROM NrmTitleAuthor, NrmTitle 
WHERE NrmTitle.title=NrmTitleAuthor.title

SELECT * FROM NrmTitleAuthor
--3D. Create a referential integrity constraint

ALTER TABLE NrmTitleAuthor
ALTER COLUMN AuthorOrder INT not null default 1

ALTER TABLE NrmTitleAuthor
ADD CONSTRAINT fk_TitleAuthorID 
FOREIGN KEY (TitleID) 
REFERENCES NrmTitle(TitleID)

--Test by attempting to update a fk value to an invalid pk value
--There is no NrmTitle.TitleID with value 500.
UPDATE NrmTitleAuthor SET TitleID = 500 WHERE TitleAuthorID = 1


--Cleanup. Remove copied columns from NrmTitle
--Very very dangerous operation!
ALTER TABLE NrmTitleAuthor
DROP COLUMN title

SELECT DISTINCT Author
INTO nrmAuthor
FROM NrmTitleAuthor

SELECT * FROM nrmAuthor

ALTER TABLE nrmAuthor
ADD AuthorID INT IDENTITY(1,1) PRIMARY KEY

ALTER TABLE NrmTitleAuthor
ADD AuthorID INT NULL

SELECT *
FROM NrmTitleAuthor, nrmAuthor
WHERE NrmTitleAuthor.Author=nrmAuthor.Author

UPDATE nrmTitleAuthor
SET NrmTitleAuthor.AuthorID=nrmAuthor.AuthorID
FROM NrmTitleAuthor, nrmAuthor
WHERE NrmTitleAuthor.Author=nrmAuthor.Author

ALTER TABLE NrmTitleAuthor
ADD CONSTRAINT fk_Author_AuthorID 
FOREIGN KEY (AuthorID) 
REFERENCES NrmAuthor(AuthorID)

SELECT *
FROM NrmTitleAuthor

ALTER TABLE NrmTitleAuthor
DROP COLUMN Author

--ALTER TABLE doc_exz
--ADD CONSTRAINT col_b_def
--DEFAULT 50 FOR column_b 

ALTER TABLE NrmTitleAuthor
ADD CONSTRAINT AuthorOrder_default 
DEFAULT 0 FOR AuthorOrder

ALTER TABLE NrmTitleAuthor
ADD CONSTRAINT AuthorOrder_Check
CHECK (AuthorOrder = 1 or AuthorOrder = 2) 

ALTER TABLE NrmTitleAuthor
ADD CONSTRAINT AuthorOrder_Check
CHECK (AuthorOrder IN(1,2)) 


SELECT * FROM NrmTitleAuthor


DROP VIEW NrmlExp1
CREATE VIEW NrmlExp1 AS
SELECT stor_name, ord_date, qty, 
      title, pub_name, type,
      publishers.State, 
     (SELECT au_lname
      FROM TitleAuthor, Authors
      WHERE Authors.au_id=TitleAuthor.au_id AND
            Titles.title_id = TitleAuthor.title_id AND 
            au_ord = 1) +
       ISNULL(', ' + 
     (SELECT au_lname
      FROM TitleAuthor, Authors
      WHERE Authors.au_id=TitleAuthor.au_id AND
            Titles.title_id = TitleAuthor.title_id AND 
            au_ord = 2), '')  +
       ISNULL(', ' + 
     (SELECT au_lname
      FROM TitleAuthor, Authors
      WHERE Authors.au_id=TitleAuthor.au_id AND
            Titles.title_id = TitleAuthor.title_id AND 
            au_ord = 3), '') AS Authors,
     (SELECT au_lname
      FROM TitleAuthor, Authors
      WHERE Authors.au_id=TitleAuthor.au_id AND
            Titles.title_id = TitleAuthor.title_id AND 
            au_ord = 1) AS Author1,
     (SELECT au_lname
      FROM TitleAuthor, Authors
      WHERE Authors.au_id=TitleAuthor.au_id AND
            Titles.title_id = TitleAuthor.title_id AND 
            au_ord = 2) AS Author2,
     (SELECT au_lname
      FROM TitleAuthor, Authors
      WHERE Authors.au_id=TitleAuthor.au_id AND
            Titles.title_id = TitleAuthor.title_id AND 
            au_ord = 3) AS Author3
FROM Stores, Sales, Titles, Publishers
WHERE Stores.stor_id=Sales.stor_id AND
      Sales.title_id=Titles.title_id AND
      Titles.pub_id=Publishers.pub_id 
UNION ALL
SELECT TOP 2 stor_name, ord_date, qty, 
      title, pub_name, type,
      publishers.State, 
     (SELECT au_lname
      FROM TitleAuthor, Authors
      WHERE Authors.au_id=TitleAuthor.au_id AND
            Titles.title_id = TitleAuthor.title_id AND 
            au_ord = 1) +
       ISNULL(', ' + 
     (SELECT au_lname
      FROM TitleAuthor, Authors
      WHERE Authors.au_id=TitleAuthor.au_id AND
            Titles.title_id = TitleAuthor.title_id AND 
            au_ord = 2), '')  +
       ISNULL(', ' + 
     (SELECT au_lname
      FROM TitleAuthor, Authors
      WHERE Authors.au_id=TitleAuthor.au_id AND
            Titles.title_id = TitleAuthor.title_id AND 
            au_ord = 3), '') AS Authors,
     (SELECT au_lname
      FROM TitleAuthor, Authors
      WHERE Authors.au_id=TitleAuthor.au_id AND
            Titles.title_id = TitleAuthor.title_id AND 
            au_ord = 1) AS Author1,
     (SELECT au_lname
      FROM TitleAuthor, Authors
      WHERE Authors.au_id=TitleAuthor.au_id AND
            Titles.title_id = TitleAuthor.title_id AND 
            au_ord = 2) AS Author2,
     (SELECT au_lname
      FROM TitleAuthor, Authors
      WHERE Authors.au_id=TitleAuthor.au_id AND
            Titles.title_id = TitleAuthor.title_id AND 
            au_ord = 3) AS Author3
FROM Stores, Sales, Titles, Publishers
WHERE Stores.stor_id=Sales.stor_id AND
      Sales.title_id=Titles.title_id AND
      Titles.pub_id=Publishers.pub_id 




/*
SELECT * FROM roysched

SELECT royalty AS LowRoyalty
FROM roysched
WHERE lorange = (SELECT MIN(lorange) FROM roysched WHERE title_id = 'PC1035') 
      AND title_id = 'PC1035'

SELECT royalty AS HighRoyalty
FROM roysched
WHERE lorange = (SELECT MAX(lorange) FROM roysched WHERE title_id = 'PC1035') 
      AND title_id = 'PC1035'

*/