– Example: some DBMS might automatically create an index(data structure to speed search) in response to PRIMARY KEY, but not UNIQUE. •MySQL creates a B+-tree index for each primary key and unique attribute – E.g., show index from Sells; – Or: show index in Sells;
153 Required Distinctions •However, standard SQL requires these distinctions: 1.There can be only one PRIMARY KEY for a relation, but several UNIQUE attributes. 2.No attribute of a PRIMARY KEY can ever be NULL in any tuple. But attributes declared UNIQUE may have NULL's, and there may be several tuples with NULL.
154 Other Declarations for Attributes •Two other declarations we can make for an attribute are: 1.NOT NULL means that the value for this attribute may never be NULL. 2.DEFAULT <value> says that if there is no specific value known for this attribute's component in some tuple, use the stated <value>.
155 Example: Default Values CREATE TABLE Drinkers ( name CHAR(30) PRIMARY KEY, addr CHAR(50) DEFAULT '123 Sesame St.', phone CHAR(16) );
156 Effect of Defaults •Suppose we insert the fact that Sally is a drinker, but we know neither her address nor her phone. •An INSERT with a partial list of attributes makes the insertion possible: INSERT INTO Drinkers(name) VALUES('Sally');
157 Effect of Defaults •But what tuple appears in Drinkers? name addr phone 'Sally' '123 Sesame St' NULL •If we had declared phone NOT NULL, this insertion would have been rejected.
158 Adding Attributes •We may change a relation schema by adding a new attribute ("column") by: ALTER TABLE <name> ADD <attribute declaration>; • Example: ALTER TABLE Bars ADD phone CHAR(16) DEFAULT 'unlisted';
159 Deleting Attributes •Remove an attribute from a relation schema by: ALTER TABLE <name> DROP <attribute>; •Example: we don't really need the license attribute for bars: ALTER TABLE Bars DROP license;
Modifying Attributes •alter table Beers modify name varchar(200); 160
Creating/dropping an index •create index sells_price_idx on Sells(price); – To drop: drop index price_idx on Sells; 161
Query execution plan •explain select * from Sells where price > 3; •Type range: only rows in a given range are retrieved •Possible keys: possible indexes to choose •Key: the index actually chosen •Using index: index used to find qualified rows in the table 162
You've reached the end of your free preview.
Want to read all 163 pages?
- Fall '14
- From Sells