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

Example some dbms might automatically create an index

This preview shows page 136 - 146 out of 146 pages.

KEY and UNIQUE. – 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 primary key – E.g., show index from Sells; – Or: show index in Sells;
Image of page 136
137 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.
Image of page 137
138 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>.
Image of page 138
139 Example: Default Values CREATE TABLE Drinkers ( name CHAR(30) PRIMARY KEY, addr CHAR(50) DEFAULT '123 Sesame St.', phone CHAR(16) );
Image of page 139
140 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');
Image of page 140
141 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.
Image of page 141
142 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';
Image of page 142
143 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;
Image of page 143
Creating/dropping an index create index sells_price_idx on Sells(price); – To drop: drop index price_idx on Sells; 144
Image of page 144
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 145
Image of page 145
Resources EXPLAIN command output format - output.html 146
Image of page 146

You've reached the end of your free preview.

Want to read all 146 pages?

  • Fall '14
  • manf, From Sells

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

Stuck? We have tutors online 24/7 who can help you get unstuck.
A+ icon
Ask Expert Tutors You can ask You can ask You can ask (will expire )
Answers in as fast as 15 minutes