DOC PREVIEW
Rose-Hulman CSSE 333 - Study Notes

This preview shows page 1-2-3-19-20-38-39-40 out of 40 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 40 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 40 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 40 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 40 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 40 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 40 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 40 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 40 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 40 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Creating Tables, DefiningConstraintsRose-Hulman Institute of TechnologyCurt CliftonOutline Data Types Creating and Altering Tables Constraints Primary and Foreign Key Constraints Row and Tuple Checks Generating Column Values Generating ScriptsData TypesSystem-supplied Data Types Numeric Integer Exact numeric Approximate numeric Monetary Date and Time Character and Unicode Character Binary OtherSlide based on MS-CreatingTables.pptUser-defined Data Types Simple, self-documenting short-hand Creating: CREATE TYPE ssnFROM varchar(11) NOT NULL Dropping: DROP TYPE ssn Advanced use: C# objectsGuidelines for Data Types If Column Length Varies, Use a VariableData Type Use tinyint Appropriately For Numeric Data Types, Commonly Usedecimal Use money for Currency Do Not Use float or real as Primary KeysSlide based on MS-CreatingTables.pptCreating and Altering TablesCreating Tables Need: Table name Column names and types Basic Example: CREATE TABLE Soda(name CHAR(20),manf CHAR(20));Header Fixed Data NB VB Variable DataNullBlockVariableBlock4 bytesDataHow SQL Server Organizes DataSlide based on MS-CreatingTables.pptA Single Data RowData rowTextTextPointerPointerRoot StructureIntermediate Node Intermediate Nodeblock 1 block 2 block 1 block 2Big @$$ DataSlide based on MS-CreatingTables.pptAltering Tables Adding columns: ALTER TABLE SodaADD msrp float; Changing columns: ALTER TABLE SodaALTER COLUMN msrp money; Dropping columns: ALTER TABLE SodaDROP COLUMN manf;Dropping Tables DROP TABLE Soda;Constraints A requirement on data elements or therelationship between data elements that theDBMS is required to enforceKinds of Constraints Primary keys (entity integrity) Foreign keys (referential integrity) Attribute-based Restrictions on the value of a single attribute Row-based Restrictions on the value of one attribute in row based onvalue of other attributes Assertions Later…Specifying Primary Key Constraint Examples: CREATE TABLE Soda (name CHAR(20) PRIMARY KEY,manf CHAR(20)); CREATE TABLE Likes(customer CHAR(30),soda CHAR(20),PRIMARY KEY(customer, soda));Foreign Key Constraints Consider foreign keys in Sells relation…Specifying Foreign Key Constraints CREATE TABLE Sells(rest CHAR(20) REFERENCES Rest(name),soda CHAR(20) REFERENCES Soda(name),price money ); or CREATE TABLE Sells(rest CHAR(20),soda CHAR(20),price money,FOREIGN KEY(rest) REFERENCES Rest(name),FOREIGN KEY(soda) REFERENCES Soda(name) );Foreign Key Restriction Referenced attributes must be either: PRIMARY KEY or else UNIQUE (another element constraint)Enforcing Foreign-Key Constraints What changes to the SodaBase data mightbreak referential integrity?Change to Table with Foreign Key How should we handle an insert or update tothe table with the foreign key that wouldbreak referential integrity?Change to Table with Primary Key How should we handle an update or delete tothe table with the primary key that wouldbreak referential integrity?3 Solutions to Primary Key Change Reject! This is the default Cascade Make same change to foreign key Set null Set foreign key to nullExample: Default Policy Suppose ‘Coke’ is referenced by Sells… We attempt to delete ‘Coke’ from Soda table Rejected! We attempt to update ‘Coke’ row, changing‘Coke’ to ‘Coca-Cola’ Rejected! Forces Sells table to be changed firstExample: Cascade Policy Suppose we delete Coke row from Soda Then automatically delete all rows for Coke fromSells Suppose we update the Coke row, changing‘Coke’ to ‘Coca-Cola’ Then automatically change all rows in Sellsreferencing Coke to reference Coca-Cola insteadExample: “Set Null” Policy Suppose we delete Coke row from Soda Then automatically change all rows referencingCoke in Sells to have nulls Suppose we update the Coke row, changing‘Coke’ to ‘Coca-Cola’ Then automatically change all rows in Sellsreferencing Coke to have nullsChoosing a Policy Can independently choose policy… For update For delete What policy should we use for… Deleting soda? Why? Updating soda name? Why?Specifying a Policy Follow foreign-key declaration with: [ON UPDATE {SET NULL | CASCADE}][ON DELETE {SET NULL | CASCADE}] Omitted clause means default policyExample CREATE TABLE Sells(rest CHAR(20) REFERENCES Rest(name)ON DELETE CASCADEON UPDATE CASCADE,soda CHAR(20) REFERENCES Soda(name)ON DELETE SET NULLON UPDATE CASCADE,price money);Attribute-based Checks Can constrain single attribute values Syntax: CHECK( condition ) Condition can use: Name of checked attribute Subqueries Checked only upon insertion, updateExample CREATE TABLE Customer(name CHAR(20) PRIMARY KEY,addr CHAR(50),phone CHAR(8) CHECK (phone LIKE'[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'));Same or Different? CREATE TABLE Sells (rest CHAR(20),soda CHAR(20) REFERENCESSoda(name),price money); CREATE TABLE Sells (rest CHAR(20),soda CHAR(20) CHECK ( soda IS NULL OR soda IN (SELECT nameFROM Soda)),price money);Row-Based Checks Can also put CHECK at end of tabledeclaration Can reference any attribute in table CHECK for each tuple… Inserted or UpdatedExample Only Joe’s can sell Coke for more than $2 CREATE TABLE Sells (rest CHAR(20),soda CHAR(20),price money,CHECK( condition )); What should condition be?Generating Column Values Table identity columns Globally unique identifiersTable Identity Column Constraint on single column of table Column must be integer or decimal data type Syntax: IDENTITY [ (seed, increment) ] Example: CREATE TABLE Users(name CHAR(20),id int IDENTITY (0, 5) );Getting Last Identity Value Use @@identity in scripts INSERT INTO Users(name)VALUE ('Molly');SELECT 'Last identity used: ' +CONVERT(char, @@identity)AS Answer;GUIDs Globally uniqueidentifiers Generated with newid()function Used with DEFAULTconstraintExample CREATE TABLE Household(HouseholdID uniqueidentifierNOT NULL DEFAULT newid(),…);Generating Scripts Can generate scripts from objects Right click database Tasks → Generate Scripts… Useful for: Storing schemas in version control system Creating test environment TrainingRecommended Practices Specify Appropriate Data Types and DataType Sizes


View Full Document

Rose-Hulman CSSE 333 - Study Notes

Documents in this Course
Load more
Download Study Notes
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Study Notes and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Study Notes 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?