Example some DBMS might automatically create an index data structure to speed

Example some dbms might automatically create an index

This preview shows page 152 - 163 out of 163 pages.

– 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;
Background image
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.
Background image
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>.
Background image
155 Example: Default Values CREATE TABLE Drinkers ( name CHAR(30) PRIMARY KEY, addr CHAR(50) DEFAULT '123 Sesame St.', phone CHAR(16) );
Background image
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');
Background image
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.
Background image
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';
Background image
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;
Background image
Modifying Attributes alter table Beers modify name varchar(200); 160
Background image
Creating/dropping an index create index sells_price_idx on Sells(price); – To drop: drop index price_idx on Sells; 161
Background image
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
Background image
Resources EXPLAIN command output format -output.html 163
Background image

You've reached the end of your free preview.

Want to read all 163 pages?

  • Fall '14
  • From Sells

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture