/*
ISC 561
ERD, 
Transforming data models into database design,
Database implementation (SQL-DDL/DML)

Chapter 6
  Steps for transforming a data model 
    into a database design pp. 193

Chapter 7
	SQL for database implementation

The following set of scripts illustrate concepts and techniques
from chapters 6 and 7.

I will hightlight portions of the text in my lecture materials

*/

/*

To execute this script, highlight and execute the CREATE DATABASE
statement and USE statement separately.
Then the run button to execute the entire script.

Statements to create, use, and drop database

CREATE DATABASE AMCIS

USE AMCIS

USE Master

DROP DATABASE AMCIS
*/

/*
Statements to drop all tables
DROP TABLE tblTableName

Note that the child tables must be dropped or deleted first 
 (assuming we have enforced the PK/FK constraint)

DROP TABLE tblPresentation
DROP TABLE tblAuthorPaper
DROP TABLE tblSession
DROP TABLE tblPaper
DROP TABLE tblAuthor
DROP TABLE tblEditor
DROP TABLE tblTrackChair
DROP TABLE tblParticipant
DROP TABLE tblInstitution
DROP TABLE tblTrack
DROP TABLE tblRoom
DROP TABLE tblTimeSlot

Why not create database and tables from GUI? Why learn the CUI?
1. CUI is faster
2. Skills are portable to multiple DBMS 
3. Scripts document database changes and can be use to recover and reverse operations

*/

/*
Statements to delete all table data
DELETE tblTableName

or...

TRUNCATE TABLE tblPresentation
TRUNCATE TABLE tblSession
TRUNCATE TABLE tblPaper
TRUNCATE TABLE tblAuthor
TRUNCATE TABLE tblEditor
TRUNCATE TABLE tblTrackChair
TRUNCATE TABLE tblParticipant
TRUNCATE TABLE tblInstitution
TRUNCATE TABLE tblTrack
TRUNCATE TABLE tblRoom
TRUNCATE TABLE tblTimeSlot
*/

/*  
1. Create a table for each entity
   CREATE TABLE pp. 239 (chapter 7)

Note: we will add constraints later. One reason for this
we may want to import legacy data that will require data cleansing.

However, because we are using surrogate keys, we will enforce primary
key constraints from the first.

Discussion of surrogate keys pp. 194. (chapter 6)

Add the 1 tables (parent) first then many tables (child)

*/
--Do not echo number of rows returned message "(n row(s) affected)"
--Instead, we will put our own custom messages.

SET NOCOUNT ON
/*
Create tables
*/

/*
Data type pp. 196 (chapter 6)

A comment on char, varchar, nchar, and nvarchar
char (stands for character)
varchar (stands for character varying)
nchar (stands for national character)
nvarchar (stands national character varying)

The "n" means the character attribute can encode the extended character set (UNICODE UCS-2)
so if you don't expect to include international characters for example, you don't 
need "n". nvarchar takes twice as much space as varchar because of support for
the extended character set.

Use char when the sizes of the column data entries are consistent.
Use varchar when the sizes of the column data entries vary considerably.
Use nchar when the sizes of the column data entries are probably going to be similar.
Use nvarchar when the sizes of the column data entries are probably going to vary considerably.


*/


CREATE TABLE tblInstitution
(
 InstitutionID INT IDENTITY(1,1) PRIMARY KEY,
 ShortName VARCHAR(3),
 LongName  NVARCHAR(50),
 Academic BIT  --domain is {0,1} which translates to {true,false}. C# requires explicit cast.
)

GO

CREATE TABLE tblParticipant
( 
 ParticipantID INT IDENTITY(1,1) PRIMARY KEY,
 FirstName NVARCHAR(100),
 LastName NVARCHAR(100),
 Email VARCHAR(50)
)

GO
--Note, these do not have primary keys. Why?
CREATE TABLE tblAuthor
(
Attending BIT
)

GO

CREATE TABLE tblEditor
(
Title VARCHAR(20),
CellPhone VARCHAR(12)
)

GO

CREATE TABLE tblTrackChair
(
AcceptanceRate INT --Assume acceptance rate is rounded up to a whole percent
)

GO

CREATE TABLE tblSession
(
 SessionID INT IDENTITY(1,1) PRIMARY KEY,
 SessionCode VARCHAR(6),
 SessionDate DATETIME,
 LCDProjectorRequired BIT
)

GO

CREATE TABLE tblPresentation
(
 PresentationID INT IDENTITY(1,1) PRIMARY KEY,
 PresentationOrder INT
)

GO

CREATE TABLE tblTrack
(
 TrackID INT IDENTITY(1,1) PRIMARY KEY,
 Title NVARCHAR(100)
)

GO

CREATE TABLE tblPaper
(
 PaperID INT IDENTITY(1,1) PRIMARY KEY,
 Title NVARCHAR(250),
 KeywordList VARCHAR(200),
 SubmissionStatus  VARCHAR(30)
)

GO
--Why make ConferenceYear Character data? Why not integer?
--Won't do math operations
--Most uses will be as string operations for queries
--Rather than cast later, just store as string now.
CREATE TABLE tblRoom
(
 RoomID INT IDENTITY(1,1) PRIMARY KEY,
 RoomName VARCHAR(20),
 MaxOccupancy INT,
 ConferenceYear CHAR(4)
)

GO

CREATE TABLE tblTimeSlot
(
 TimeSlotID INT IDENTITY(1,1) PRIMARY KEY,
 TimeBegin DATETIME,
 TimeEnd DATETIME,
 ConferenceYear CHAR(4)
)
GO



--Echo a print statement that confirms result.
PRINT 'Tables successfully constructed...'

SET NOCOUNT OFF