Docstoc

DDLexercises

Document Sample
DDLexercises Powered By Docstoc
					DDL (Data Definition Exercises): EXERCISE 1. Create a database called TEMP using SQL Enterprise Manager and generate the script Using SQL Server Enterprise Manager. To generate script click Tools item on menu then click generate scripts. Then follow instructions.

EXERCISE 2. The example below shows complete table definitions with all the constraint definitions for the three tables (jobs, employee, and publishers) created in the pubs database. In the temp database, created in EXERCISE 1, create the three tables using a batch file by using the script below. To do that, Copy and Paste the code below into a text file. Name the file, pubs.sql. Be sure to use the extension sql for all SQL batch files. Use the SQL Server Query Analyzer by clicking Tools in SQL Enterprise Manager then click the open icon. Load pubs.sql and run it. Look at the constraints generated via the Graphical Interface. Right click each table in turn in SQL Server Enterprise Manager Drop all three tables then create the three tables again with as many constraints as possible using SQL Enterprise Manager Graphical Interface and generate the corresponding script file. To do this, in the SQL Server Enterprise Manager, click the Tools menu item then click generate scripts. Look at the constraints generated again and compare the generated script to the script below. Notice that many constraints are missing. The lesson is that even though you may generate the initial script file via the Graphical Interface it will be incomplete and usually must be modified. CREATE TABLE publishers ( pub_id char(4) NOT NULL CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756') OR pub_id LIKE '99[0-9][0-9]'), pub_name city varchar(40) varchar(20) NULL,

NULL,

state country

char(2) NULL, varchar(30) NULL

DEFAULT('USA') ) CREATE TABLE jobs ( job_id smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, job_desc varchar(50) NOT NULL

DEFAULT 'New Position - title not formalized yet', min_lvl tinyint NOT NULL CHECK (min_lvl >= 10), max_lvl tinyint NOT NULL CHECK (max_lvl <= 250) )

/* ************************* employee table ************************* */ CREATE TABLE employee ( emp_id char(9) CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED CONSTRAINT CK_emp_id CHECK (emp_id LIKE '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'),

fname varchar(20) minit char(1) NULL, lname varchar(30) job_id smallint DEFAULT 1

NOT NULL,

NOT NULL, NOT NULL

/* Entry job_id for new hires. */ REFERENCES jobs(job_id), job_lvl tinyint DEFAULT 10, /* Entry job_lvl for new hires. */ pub_id char(4) NOT NULL DEFAULT ('9952') REFERENCES publishers(pub_id), /* By default, the Parent Company Publisher is the company

to whom each employee reports. */ hire_date datetime NOT NULL

DEFAULT (getdate()) /* By default, the current system date will be entered. */ )

EXERCISE 3. Create a new database called SPJ. Using the script below create and populate the 4 tables named S, P, J and SPJ. We will be using this database to do most of our data retrieval exercises. S table stands for Suppliers; P for Parts and J for Jobs (Projects) and SPJ hold the shipment records. Namely which supplier shipped which part to which job on which date and the quantity of shipment? Here is the script:
create table S (sNo char(2) PRIMARY KEY, name char(5), status NUMERIC(2), city char(6)); create table P (pNo char(2) PRIMARY KEY, name char(5), color char(5), weight NUMERIC(2), city char(6)); create table J (jNo char(2) PRIMARY KEY, name char(8), city char(6)); create table SPJ (s# char(2), p# char(2), j# char(2), shipDate datetime, qty NUMERIC(3), PRIMARY KEY(s#,p#,j#)); INSERT VALUES INSERT VALUES INTO S ('S1','Smith',20,'London'); INTO S ('S2','Jones',10,'Paris');

insert values insert values insert values insert values insert values insert values insert values insert values insert values insert values insert values insert values insert values insert values insert values insert values insert values insert values insert values insert values insert values insert values insert values insert values insert values insert values insert values insert

into S ('S3','Blake',30,'Paris'); into S ('S4','Clark',20,'London'); into S ('S5','Adams',30,'Athens'); into P ('P1','Nut','Red',12,'London'); into P ('P2','Bolt','Green',17,'Paris'); into P ('P3','Screw','Blue',17,'Rome'); into P ('P4','Screw','Red',14,'London'); into P ('P5','Cam','Blue',12,'Paris'); into P ('P6','Cog','Red',19,'London'); into J ('J1','Sorter','Paris'); into J ('J2','Punch','Rome'); into J ('J3','Reader','Athens'); into J ('J4','Console','Athens'); into J ('J5','Collator','London'); into J ('J6','Terminal','Oslo'); into J ('J7','Tape','London'); into SPJ ('S1','P1','J1','3/3/99',200); into SPJ ('S1','P1','J4','4/4/88',700); into SPJ ('S2','P3','J1','6/6/88',400); into SPJ ('S2','P3','J2','8/8/99',200); into SPJ ('S2','P3','J3','7/7/77',200); into SPJ ('S2','P3','J4','8/9/99',500); into SPJ ('S2','P3','J5','8/7/99',600); into SPJ ('S2','P3','J6','8/7/89',400); into SPJ ('S2','P3','J7','8/7/89',800); into SPJ ('S2','P5','J2','8/8/88',100); into SPJ ('S3','P3','J1','9/9/99',200); into SPJ

values insert values insert values insert values insert values insert values insert values insert values insert values insert values insert values insert values insert values

('S3','P4','J2','9/8/99',500); into SPJ ('S4','P6','J3','9/8/99',300); into SPJ ('S4','P6','J7','9/9/99',300); into SPJ ('S5','P2','J2','9/8/99',200); into SPJ ('S5','P2','J4','9/8/99',100); into SPJ ('S5','P5','J5','7/8/99',500); into SPJ ('S5','P5','J7','6/6/00',100); into SPJ ('S5','P6','J2','9/7/00',200); into SPJ ('S5','P1','J4','9/8/88',100); into SPJ ('S5','P3','J4','7/8/99',200); into SPJ ('S5','P4','J4','8/8/99',800); into SPJ ('S5','P5','J4','7/8/99',400); into SPJ ('S5','P6','J4','7/8/00',500);

EXERCISE 4. Using Alter Table to add three referential integrity constraints. Namely that S#, P#, J# in table SPJ reference sNo in table S, pNo in table P and jNo in table J respectively.

EXERCISE 6. Create an Entity-relationship Diagram for the SPJ database. In SQL Enterprise Manager, expand Microsoft SQL Servers folder, then expand Databases folder, then expand the SPJ database folder. Click on Diagrams icon then create an Entity-Relationship diagram by including all four tables in the SPJ database. Now connect each Foreign Key in SPJ to its corresponding Primary Key in respective tables S, P, J. You do this by depressing the left mouse button and dragging the arrow from each Foreign Key to its corresponding Primary Key. For more detail see page 39 – 40 in the text.

EXERCISE 7(The Database below is used throughout the text.) Follow the instructions starting on page 32 in the text to create Fund Accounts Database and create Accounts table. Generate the script that creates the Accounts table and compare it with the following script.
/* CREATE TABLE [dbo].[Accounts] ( [acct_no] [varchar] (10) NOT NULL , [acct_lastname] [varchar] (70) NOT NULL , [acct_firstname] [varchar] (50) NULL , [last_contact] [datetime] NULL , [addr_line1] [varchar] (30) NULL , [addr_line2] [varchar] (30) NULL , [addr_line3] [varchar] (30) NULL , [city] [varchar] (30) NULL , [state_abbr] [char] (2) NOT NULL , [zip] [varchar] (10) NOT NULL , [home_phone] [varchar] (15) NULL , [work_phone] [varchar] (15) NULL , [tot_assets] [money] NOT NULL , [fiscal_period] [char] (2) NOT NULL , [cycle] [tinyint] NOT NULL , [acct_type] [varchar] (6) NOT NULL , [acct_status] [varchar] (8) NOT NULL , [manager_num] [smallint] NULL , [acct_note] [text] NULL , CONSTRAINT [PK_Accounts] PRIMARY KEY NONCLUSTERED ( [acct_no] ), CONSTRAINT [IX_Accounts] UNIQUE CLUSTERED ( [acct_no] ), CONSTRAINT [CK_Accounts] CHECK ([acct_type] = 'Tax' or [acct_type] = 'Nontax') )

*/
-- Create the remaining tables in the database using the script below. The table definitions for Fund Accounts database are listed in Appendix B on page 383. CREATE TABLE [dbo].[AccountValues] ( [acct_no] [varchar] (10) NOT NULL , [stmt_date] [datetime] NOT NULL , [invest_tot] [money] NOT NULL CONSTRAINT [DF__AccountValues__invest_tot__3BFFE745] DEFAULT (0), [cur_value] [money] NOT NULL CONSTRAINT [DF__AccountValues__cur_value__3CF40B7E] DEFAULT (0) ) GO GRANT REFERENCES , SELECT , INSERT , [dbo].[dtproperties] TO [public] DELETE , UPDATE ON

GO CREATE TABLE [dbo].[FundAccounts] ( [fund_cd] [char] (5) NOT NULL , [fundacctno] [char] (35) NOT NULL , [acct_no] [varchar] (10) NOT NULL , [acct_name] [varchar] (70) NULL , [acct_type] [char] (4) NULL , CONSTRAINT [PK_FundAccounts] PRIMARY KEY NONCLUSTERED ( [fund_cd], [fundacctno] ), CONSTRAINT [FK_FundAccounts_Accounts] FOREIGN KEY ( [acct_no] ) REFERENCES [dbo].[Accounts] ( [acct_no] ), CONSTRAINT [FK_FundAccounts_Funds] FOREIGN KEY ( [fund_cd] ) REFERENCES [dbo].[Funds] ( [fund_cd] ) ) GO CREATE TABLE [dbo].[Funds] ( [fund_cd] [char] (5) NOT NULL , [fund_name] [varchar] (45) NOT NULL , CONSTRAINT [PK_Funds] PRIMARY KEY NONCLUSTERED ( [fund_cd] ) ) GO CREATE TABLE [dbo].[Statements] ( [acct_no] [varchar] (10) NOT NULL , [stmt_date] [datetime] NOT NULL , [invest_tot] [money] NOT NULL CONSTRAINT [DF_Statements_invest_tot] DEFAULT (0), [cur_value] [money] NOT NULL CONSTRAINT [DF_Statements_cur_value] DEFAULT (0), [invest_gain] [numeric](18, 0) NOT NULL CONSTRAINT [DF_Statements_invest_gain] DEFAULT (0), [tot_return] [numeric](18, 0) NOT NULL CONSTRAINT [DF_Statements_tot_return] DEFAULT (0), [ytd_tot_return] [numeric](18, 0) NOT NULL CONSTRAINT [DF_Statements_ytd_tot_return] DEFAULT (0), [tax_income] [money] NOT NULL CONSTRAINT [DF_Statements_tax_income] DEFAULT (0), [taxfree_income] [money] NOT NULL CONSTRAINT [DF_Statements_taxfree_income] DEFAULT (0), [tax_cap_gain] [money] NOT NULL CONSTRAINT [DF_Statements_tax_cap_gain] DEFAULT (0),

[taxfree_cap_gain] [money] NOT NULL CONSTRAINT [DF_Statements_taxfree_cap_gain] DEFAULT (0), [stmt_tot] [money] NOT NULL CONSTRAINT [DF_Statements_stmt_tot] DEFAULT (0), [ytd_tax_income] [money] NOT NULL CONSTRAINT [DF_Statements_ytd_tax_income] DEFAULT (0), [ytd_taxfree_income] [money] NOT NULL CONSTRAINT [DF_Statements_ytd_taxfree_income] DEFAULT (0), [ytd_tax_cap_gain] [money] NOT NULL CONSTRAINT [DF_Statements_ytd_tax_cap_gain] DEFAULT (0), [ytd_taxfree_cap_gain] [money] NOT NULL CONSTRAINT [DF_Statements_ytd_taxfree_cap_gain] DEFAULT (0), [ytd_tot] [money] NOT NULL CONSTRAINT [DF_Statements_ytd_tot] DEFAULT (0), CONSTRAINT [PK_Statements] PRIMARY KEY NONCLUSTERED ( [acct_no], [stmt_date] ), CONSTRAINT [FK_Statements_Accounts] FOREIGN KEY ( [acct_no] ) REFERENCES [dbo].[Accounts] ( [acct_no] ) ) GO CREATE TABLE [dbo].[TranHistory] ( [fund_cd] [char] (5) NOT NULL , [fundacctno] [char] (35) NOT NULL , [trandate] [datetime] NOT NULL , [trantype] [varchar] (10) NOT NULL , [divamt] [money] NULL , [amount] [money] NULL , [sh_price] [money] NULL , [sh_num] [decimal](18, 4) NULL , [sh_tot] [decimal](18, 4) NULL , [inv_tot] [money] NULL , [inv_sh] [decimal](18, 4) NULL , [cost_tot] [money] NULL , [cost_sh] [money] NULL , [tax_gain] [money] NULL , [inv_gain] [money] NULL , CONSTRAINT [PK_TranHistory] PRIMARY KEY NONCLUSTERED ( [fund_cd], [fundacctno], [trandate], [trantype] ), CONSTRAINT [FK_TranHistory_FundAccounts] FOREIGN KEY ( [fund_cd], [fundacctno] ) REFERENCES [dbo].[FundAccounts] ( [fund_cd],

[fundacctno] ) ) GO

EXERCISE 7 Use the BCP utility, see pages 41 - 46 in text, and the files in Ch2 folder in the text CD to populate the tables in the Fund Accounts Database.


				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:6
posted:12/25/2009
language:English
pages:10