Agenda for Class 2/16/2012

1. Agenda for Class 2/16/2012 • Introduce Microsoft’s SQL Server database management system. • Use the lab to discuss how to CREATE, DROP and populate (INSERT) tables. • Introduce SELECT statement. • Discuss connection between MS Access and SQL Server.

2. Accessing SQL Server • Login to COBA server (use your netID). • Accessing from COB labs: • Execute Microsoft SQL Server Management Studio. • Accessing from home: • Execute Remote Desktop Connection. • Login to server called sts.coba.unr.edu • Use your netID as the User name and password. • Log on to: BSQL\Students • Execute Microsoft SQL Server Management Studio.

3. SQL Server Management Studio • Object Explorer • New Query • File Tabs • Difference between database object and SQL code • Accessing files from the classdata area • Saving SQL code to/from a separate file

4. Class Exercise Database Referential integrity: Table constraint. When inserting data in the child table, checks to see whether a related row exists in the parent table.

5. Let’s create a small table • Click on the “new query” button. • Type the following: CREATE TABLE tblemployee (employeeID char(5) primary key, LastName varchar(30), BillingRate money); • Click on the “Execute” button.

6. Deleting a table • Cannot have more than one data object with the same name. • Must delete data objects before re-creating them. • SQL Statement is: DROP TABLE tblemployee; • Let’s try and drop the table named tblemployee. • Must delete objects in the order of referential integrity constraints.

7. Create a named constraint • Constraints can be “named” in SQL for easier future reference. CREATE TABLE tblemployee (employeeID char(4), LastName varchar(30), BillingRate money, Constraint pkemployee Primary key (employeeID));

8. SQL INSERT Statement • Used to “populate” a table with data. • Used to enter one row of data. • Character/string data must be entered surrounded by single quotes. • Dates can be entered using a variety of formats: • 16-feb-2012 • 02/16/2012 • 02-16-2012

9. Let’s put data into that table • Click on the “new query” button. • Type the following SQL Commands (or copy from the PowerPoint). Each command will produce one row in the table called “tblemployee”. INSERT INTO tblemployee VALUES (‘7819', ‘Martinson’, 125); INSERT INTO tblemployee VALUES (‘2745', ‘Johnson’, 85.50); INSERT INTO tblemployee VALUES (‘0062', Belwin, 54.75); INSERT INTO tblemployee VALUES (0062, ‘Smith’, 200);

10. Let’s look at the data • Click on the “new query” button. • Type the following SQL command: SELECT * FROM tblemployee;

11. CREATE TABLE tblContract (ContractID char(6), DateSigned datetime, DateDue datetime, CONSTRAINT pkcontract PRIMARY KEY (ContractID)); CREATE TABLE tblTime (EmployeeID char(4), DateTimeStarted datetime, ContractID char(6), Amount int, CONSTRAINT pkTime PRIMARY KEY (employeeID, datetimestarted), CONSTRAINT fktoContract FOREIGN KEY (contractid) REFERENCES tblContract(contractID), CONSTRAINT fktoemployee FOREIGN KEY (employeeID) REFERENCES tblemployee(employeeID));

12. Enter data • Populate the two tables with INSERT statements. • This means completing task #3 on the exercise

13. Sample Tables with Concatenated Foreign Key

14. CREATE TABLE tblOrd (order_id CHAR(5) PRIMARY KEY, cust_id CHAR(3), ord_date DATETIME); CREATE TABLE tblProduct (prod_id INT, manufacturer_id CHAR(6), location_id INT, cost MONEY, PRIMARY KEY (prod_id, manufacturer_id, location_id));

15. CREATE TABLE tblOrderLine (order_id CHAR(5), prod_id INT, ship_date DATETIME, manufacturer_id CHAR(6), location_id INT, qty DECIMAL(8,2), price MONEY, PRIMARY KEY (order_id,prod_id,ship_date), constraintproduct_fk Foreign key (prod_id,manufacturer_id,location_id) References tblProduct(prod_id,manufacturer_id,location_id), CONSTRAINT order_fk FOREIGN KEY (order_id) REFERENCES tblOrd(order_id));

16. Creating tables with other constraints create tabletblExample (field1 INT primary key, field2 Char(2) CHECK (code_field in(‘02’,’A1’,’B3’,’04’)), field3 MONEY check (cost > .02), field4 char(5) NOT NULL, field5 decimal(6,4) default 1 );

17. SQL Server Connections • Not a “front end” package; not designed for end-users. • Can be connected as a backend database to a variety of different packages. • Access is the most convenient for our environment. • Pull out printed handout on connection to MS Access.

18. Examples of Retrieving Data from a Table SELECT * FROM tblemployee; SELECT * FROM tblcontract SELECT contractID, datedue FROM tblcontract; SELECT employeeID, lastname FROM tblemployee WHERE employeeID = ‘0062’; The * means retrieve all columns. The FROM statement, without a WHERE statement, means retrieve all rows.

19. Copy the results into Word • Select all the columns and rows from tblemployee. • Copy and paste the results from SQL Server into Word • See how ugly? • Could do a screen capture to improve the look.

20. Using MS Access as our “front end” • Open a file on the “k” drive called: IS475\s12\StoredProcedureTemplate. CREATE PROCEDURE --Stored Proc Name Here AS --Put your code here GO

21. Using an MS Access Project for Output • Refer to the “Connecting SQL Server 2008 to MS Access 2010” link on the course web site. • Follow the instructions to create a query that can be retrieved from MS Access. • Look at the output in MS Access. • Copy and paste output from MS Access to Word.

22. Sharing tables with your team • Discuss database owners. • Discuss database name in object browser. • Discuss permissions. • Best practice: Create and populate the tables in each group member’s database. The data for HW#5-7 is relatively static, so it will lessen the complexity of the assignments if everyone has direct access to the tables.