Basic structures: rows and tables
Representing data in rows
Each real-world individual of a class (for example, each customer who does business with our enterprise) is represented by a row of information in a database table. The row is defined in the relational model as a tuple that is constructed over a given scheme. Mathematically, the tuple is a function that assigns a constant value from the attribute domain to each attribute of the scheme. Notice that because the scheme is a set of attributes, we could show them in any order without changing the meaning of the data in the row (tuple).
In formal notation, we could show the assignments explicitly, where t represents a tuple:
In practice, when we create a table row in SQL, we are actually making the assignment of domain values to attributes, just as in the tuple definition.
INSERT INTO customers (cfirstname, clastname, cphone, cstreet, czipcode) VALUES ('Tom', 'Jewett', '714-555-1212', '10200 Slater', '92708');
In SQL, you can omit the attribute names from the INSERT INTO statement, as long as you keep the comma-delimited list of values in exactly the same order that was used to create the table.
When we change the data in a table row using SQL, we are also following the tuple definition of assigning domain values to attributes.
UPDATE customers SET cphone = '714-555-2323' WHERE cphone = '714-555-1212';
A database table is simply a collection of zero or more rows. This follows from the relational model definition of a relation as a set of tuples over the same scheme. (The name “relational model” comes from the relation being the central object in this model.)
• Knowing that the relation (table) is a set of tuples (rows) tells us more about this structure, as we saw with schemes and domains.
Insuring unique rows
Since each row in a table must be unique, no two rows can have exactly the same values for every one of their attributes. Therefore, there must be some set of attributes (it might be the set of all attributes) in each relation whose values, taken together, guarantee uniqueness of each row. Any set of attributes that can do this is called a super key (SK). Super keys are a property of the relation (table), filled in with any reasonable set of real-world data, even though we show them in the relation scheme drawing for convenience.
The database designer picks one of the possible super key attribute sets to serve as the primary key (PK) of the relation. (Notice that the PK is an SK, but not all SKs are PKs!) The PK is sometimes also called a unique identifier for each row of the table. This is not an arbitrary choice—we’ll discuss it in detail on a later page. For our customers table, we’ll pick the customer’s first name, last name, and phone number. We are likely to have at least two customers with the same first and last name, but it is very unlikely that they will both have the same phone number.
In SQL, we specify the primary key with a constraint on the table that lists the PK attributes. We also give the constraint a name that is easy for us to remember later (like “customers_pk” here).
ALTER TABLE customers ADD CONSTRAINT customers_pk PRIMARY KEY (cfirstname, clastname, cphone);
We also can specify the primary key when we create the table. The NOT NULL constraint prevents the PK attributes from being left empty, since NULL is a special constant in database systems that means “this field doesn’t have any value assigned to it.” It’s not the same as a zero length string or the number zero.
CREATE TABLE customers ( cfirstname VARCHAR(20) NOT NULL, clastname VARCHAR(20) NOT NULL, cphone VARCHAR(20) NOT NULL, cstreet VARCHAR(50), czipcode VARCHAR(5)), CONSTRAINT customers_pk PRIMARY KEY (cfirstname, clastname, cphone);
Copyright © 2002–2006, by Tom Jewett. Links to this site are welcome and encouraged. Individual copies may be printed for non-commercial classroom or personal use; however, this material may not be reposted to other web sites or newsgroups, or included in any printed or electronic publication, whether modified or not, without specific permission from the author.