MIS 431

1
MIS 431

• Chapter 3.
• Working withTables and Views

2
Practical Exercise 1

• In the 1048 instance of SQL Server
• Add logins for each employee of SS
• Debra, Kenny, Lucy, Ollie, Paula, Ruth, Ullie,
  ZaZa
• The login name should be your initials employee
  first name e.g., SRDebra
• Grant public access to each login to your
  database.

3
Types of Tables

• Permanent tables
• Including sys tables
• sysobjects
• syscolumns
• Temporary tables
• Local tablename
• Global tablename
• Table variables
• DECLARE _at_tablename

4
Creating Tables

• CREATE TABLE statement
• Upper limits
• 1024 columns (fields)
• 8060 bytes total record length
• Stored procedures
• sp_help
• sp_spaceused
• sp_rename

5
Field Characteristics

• Nullability
• Identity
• _at__at_IDENTITY function that returns last-assigned
  identity value for this session
• Default
• Necessary when adding a NOT NULL field to an
  existing table

6
Practical Exercise 2

• In your database
• Create a table (tblUserID) to hold SQL user
  identification numbers and person identification
  numbers.
• Person ID numbers are a foreign key to tblPerson
• SQL user ID numbers are a foreign key to sysusers
• Neither field can be null
• Both ID numbers are unique (within your database)
• A person might have more than one SQL user ID
• Populate your table

7
Views

• A view is basically a predefined query (a SELECT
  statement) that is stored in the database for
  later use. (Guerrero Rojas, p. 107)
• Benefits
• Security layer
• Partition the data
• Combine data from many sources into one logical
  object

8
Creating Views

• CREATE VIEW statement
• Can reference up to 1024 columns
• WITH ENCRYPTION
• WITH SCHEMABINDING
• WITH CHECK OPTION
• Stored procedures
• sp_help
• sp_helptext
• sp_depends

9
Modifying Data in Views

• May update only one table at a time
• View can be composed of fields from many tables
• Only fields in a single table can be modified by
  a given INSERT or UPDATE command
• May delete records in only one table
• View can be composed of fields from only a single
  table

10
Practical Exercise 3

• In your database
• Create a view (vueUserID) that returns SQL User
  IDs, first and last names, and person
  identification numbers of employees.
• Requires that you join tblPerson to tblUserID
• Use the subtype discriminator field in tblPerson
  to limit output to employees