1> /* Sample output file for tables created following directions*/
2> /* in lab guide, Sept. 30th. */
3> /* */
4> /* Will be creating database objects - go to my database. */
5> /* */
6> use evelyn
1> /* Drop existing tables so I can make changes. */
2> /* */
3> drop table eventinfo
1> drop table contactinfo
1> /* Create tables with constraints. Since eventinfo will */
2> /* reference contactinfo, create contactinfo first. */
3> /* */
4> /* Create table to store contact data. In addition to */
5> /* primary key constraint, will allow for neighboring states*/
6> /* only, and will ensure correct zip code formatting. */
7> /* */
8> create table contactinfo
9> (contactID tinyint not null
10> constraint contactid_pk primary key,
11> fname varchar(20) not null,
12> lname varchar(20) not null,
13> num_street varchar(20) null,
14> city varchar(20) null,
15> state char(2) not null
16> constraint statechk check
17> (state in ('DE', 'NJ', 'PA', 'MD')),
18> zip varchar(10) null
19> constraint zipchk check
20> (zip like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
21> or zip like '[0-9][0-9][0-9][0-9][0-9]'),
22> phone char(12) not null)
1> /* */
2> /* Create table to store event data. Add column to reference*/
3> /* contactID; add default date, time, and ticket price. */
4> /* */
5> create table eventinfo
6> (eventID smallint not null
7> constraint eventid_pk primary key,
8> contactID tinyint not null
9> references contactinfo (contactID),
10> eventname varchar(40) not null,
11> type varchar(20) null,
12> eventdate smalldatetime default getdate() not null,
13> eventtime smalldatetime default '6 pm' null,
14> location varchar(40) null,
15> ticket smallmoney default 0 null,
16> attendance smallint null)
1> /* */
2> /* Insert some complete rows into each table, contactinfo */
3> /* first, so that the contact references exists when needed.*/
4> /* */
5> insert into contactinfo
6> values (1, 'Eliza', 'Doolittle', '123 Albert Square', 'Newark',
7> 'DE', '19700', '302-368-0000')
8> insert into contactinfo
9> values (2, 'Henry', 'Higgins', '321 Victoria Circle', 'Newark',
10> 'DE', '19700-1234', '731-0000')
(1 row affected)
(1 row affected)
1> /* */
2> insert into eventinfo
3> values (1, 1, 'Community Days', 'Outdoor fair', '9/8/2000', '10:00 am',
4> 'University Mall', 1.00, 5000)
5> insert into eventinfo
6> values (2, 1, 'Halloween Parade', 'Parade', 'Oct 31 2000', '6 pm',
7> 'Main St., Newark', 1.00, 200)
8> insert into eventinfo
9> values (3, 2, 'Fireman''s Barbecue', 'Fundraiser', '11/10/2000', '5 pm',
10> 'Newark Senior Center', 15, 500)
(1 row affected)
(1 row affected)
(1 row affected)
1> /* */
2> /* Show that tables now contain data. */
3> /* */
4> select * from eventinfo
eventID contactID eventname
type eventdate
eventtime location
ticket attendance
------- --------- ----------------------------------------
-------------------- --------------------------
-------------------------- ----------------------------------------
------------------------ ----------
1 1 Community Days
Outdoor fair Sep 8 2000 12:00AM
Jan 1 1900 10:00AM University Mall
1.00 5000
2 1 Halloween Parade
Parade Oct 31 2000 12:00AM
Jan 1 1900 6:00PM Main St., Newark
1.00 200
3 2 Fireman's Barbecue
Fundraiser Nov 10 2000 12:00AM
Jan 1 1900 5:00PM Newark Senior Center
15.00 500
(3 rows affected)
1> /* */
2> select * from contactinfo
contactID fname lname num_street
city state zip phone
--------- -------------------- -------------------- --------------------
-------------------- ----- ---------- ------------
1 Eliza Doolittle 123 Albert Square
Newark DE 19700 302-368-0000
2 Henry Higgins 321 Victoria Circle
Newark DE 19700-1234 731-0000
(2 rows affected)
1> /* */
2> /* Test constraints and defaults. */
3> /* Add row with existing ID to contactinfo. */
4> /* */
5> insert into contactinfo
6> values (1, 'Freddie', 'Eynsford-Hill', '246 Oxford Street', 'Oxford',
7> 'PA', '19735', '610-555-5555')
Msg 2601, Level 14, State 3:
Line 5:
Attempt to insert duplicate key row in object 'contactinfo' with unique index
'contactid_pk'
Command has been aborted.
(0 rows affected)
1> /* */
2> /* Insert an event without a contact. */
3> /* */
4> insert into eventinfo
5> values (4, 3, 'Midnight Dance', 'Non-event', '10/08/2000', '12 am',
6> 'Trabant U. Ctr.', 10, 350)
Msg 546, Level 16, State 1:
Line 4:
Foreign key constraint violation occurred, dbname = 'evelyn', table name =
'eventinfo', constraint name = 'eventinfo_contac_1564532607'.
Command has been aborted.
(0 rows affected)
1> /* */
2> /* Try _not_ inserting data into a required column. */
3> /* */
4> insert into contactinfo
5> (contactID, fname, lname, num_street, city, state, zip)
6> values (3, 'Stanley', 'Pickering', '456 Picadilly Circus', 'London',
7> 'PA', '19735')
Msg 233, Level 16, State 2:
Line 4:
The column phone in table contactinfo does not allow null values.
1> /* */
2> /* Insert a row without specifying data where defaults exist.*/
3> /* */
4> insert into eventinfo
5> (eventID, contactID, eventname, type, location, attendance)
6> values (4, 2, 'Harvest Fling', 'Dance', 'Student Center', 500)
(1 row affected)
1> /* */
2> /* Add a row with an incorrectly formatted zip code. */
3> /* */
4> insert into contactinfo
5> values (4, 'Mabel', 'Eynsford-Hill', '246 Oxford Street', 'Oxford',
6> 'PA', '19375-abcd', '610-555-5555')
Msg 548, Level 16, State 1:
Line 4:
Check constraint violation occurred, dbname = 'evelyn', table name =
'contactinfo', constraint name = 'zipchk'.
Command has been aborted.
(0 rows affected)
1> /* */
2> /* Add a contact from Florida. */
3> /* */
4> insert into contactinfo
5> values (5, 'Minnie', 'Mouse', 'The Enchanted Village', 'Orlando',
6> 'FL', '99999-9999', '123-123-1234')
Msg 548, Level 16, State 1:
Line 4:
Check constraint violation occurred, dbname = 'evelyn', table name =
'contactinfo', constraint name = 'statechk'.
Command has been aborted.
(0 rows affected)
1> /* */
2> /* Select all again - expect one new row with defaults. */
3> /* */
4> select * from eventinfo
eventID contactID eventname
type eventdate
eventtime location
ticket attendance
------- --------- ----------------------------------------
-------------------- --------------------------
-------------------------- ----------------------------------------
------------------------ ----------
1 1 Community Days
Outdoor fair Sep 8 2000 12:00AM
Jan 1 1900 10:00AM University Mall
1.00 5000
2 1 Halloween Parade
Parade Oct 31 2000 12:00AM
Jan 1 1900 6:00PM Main St., Newark
1.00 200
3 2 Fireman's Barbecue
Fundraiser Nov 10 2000 12:00AM
Jan 1 1900 5:00PM Newark Senior Center
15.00 500
4 2 Harvest Fling
Dance Oct 11 2000 11:28PM
Jan 1 1900 6:00PM Student Center
0.00 500
(4 rows affected)
1> /* */
2> select * from contactinfo
contactID fname lname num_street
city state zip phone
--------- -------------------- -------------------- --------------------
-------------------- ----- ---------- ------------
1 Eliza Doolittle 123 Albert Square
Newark DE 19700 302-368-0000
2 Henry Higgins 321 Victoria Circle
Newark DE 19700-1234 731-0000
(2 rows affected)
1> /* */
2> /* Show information about tables using Sybase stored procedure.*/
3> /* */
4> sp_help eventinfo
Name Owner
Type
------------------------------ ------------------------------
----------------------
eventinfo dbo
user table
(1 row affected)
Data_located_on_segment When_created
------------------------------ --------------------------
default Oct 11 2000 11:27PM
Column_name Type Length Prec
Scale Nulls Default_name
Rule_name Identity
------------------------------ ------------------------------ ------ ----
----- ----- ------------------------------
------------------------------ --------
eventID smallint 2 NULL
NULL 0 NULL
NULL 0
contactID tinyint 1 NULL
NULL 0 NULL
NULL 0
eventname varchar 40 NULL
NULL 0 NULL
NULL 0
type varchar 20 NULL
NULL 1 NULL
NULL 0
eventdate smalldatetime 4 NULL
NULL 0 eventinfo_eventd_1516532436
NULL 0
eventtime smalldatetime 4 NULL
NULL 1 eventinfo_eventt_1532532493
NULL 0
location varchar 40 NULL
NULL 1 NULL
NULL 0
ticket smallmoney 4 NULL
NULL 1 eventinfo_ticket_1548532550
NULL 0
attendance smallint 2 NULL
NULL 1 NULL
NULL 0
index_name index_description
index_keys
index_max_rows_per_page
-------------------- --------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------
eventid_pk clustered, unique located on default
eventID
0
(1 row affected)
No defined keys for this object.
Msg 18085, Level 16, State 1:
Procedure 'sp_helpartition', Line 83:
Object is not partitioned.
(return status = 0)
1> /* */
2> sp_help contactinfo
Name Owner
Type
------------------------------ ------------------------------
----------------------
contactinfo dbo
user table
(1 row affected)
Data_located_on_segment When_created
------------------------------ --------------------------
default Oct 11 2000 11:27PM
Column_name Type Length Prec Scale Nulls Default_name
Rule_name Identity
--------------- --------------- ------ ---- ----- ----- ---------------
--------------- --------
contactID tinyint 1 NULL NULL 0 NULL
NULL 0
fname varchar 20 NULL NULL 0 NULL
NULL 0
lname varchar 20 NULL NULL 0 NULL
NULL 0
num_street varchar 20 NULL NULL 1 NULL
NULL 0
city varchar 20 NULL NULL 1 NULL
NULL 0
state char 2 NULL NULL 0 NULL
statechk 0
zip varchar 10 NULL NULL 1 NULL
zipchk 0
phone char 12 NULL NULL 0 NULL
NULL 0
index_name index_description
index_keys
index_max_rows_per_page
-------------------- --------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------
contactid_pk clustered, unique located on default
contactID
0
(1 row affected)
No defined keys for this object.
Msg 18085, Level 16, State 1:
Procedure 'sp_helpartition', Line 83:
Object is not partitioned.
(return status = 0)
1> /* */
2> /* Free up space: */
3> /* */
4> dump tran evelyn with truncate_only