Unformatted text preview:

The Relational ModelImportant PointsTablesCREATE TABLE StatementCommon Data TypesRelationshipsAlternative TerminologyRelational Model DefinitionsExampleTry ItDatabaseFormal DefinitionRelation Schemas and InstancesCartesian Product (review)Relation InstanceProperties of RelationsRelational KeysExample RelationsExample Relation InstancesPractice QuestionsRelational IntegrityForeign Keys ExampleIntegrity QuestionsGeneral ConstraintsThe Relational ModelMuch of the material presented in these slides was developed by Dr. Ramon Lawrence at the University of IowaImportant PointsRelational Model DefinitionsRelational KeysRelational IntegrityTablesRelational database is a collection of tablesHeading: table name and column namesBody: rows, occurrences of dataStdSSN StdLastName StdMajor StdClass StdGPA 123-45-6789 WELLS IS FR 3.00 124-56-7890 NORBERT FIN JR 2.70 234-56-7890 KENDALL ACCT JR 3.50 StudentCREATE TABLE StatementCREATE TABLE Student( StdSSN CHAR(11), StdFirstName VARCHAR(50), StdLastName VARCHAR(50),StdCity VARCHAR(50), StdState CHAR(2),StdZip CHAR(10), StdMajor CHAR(6), StdClass CHAR(6), StdGPA DECIMAL(3,2) )Common Data TypesCHAR(L)VARCHAR(L)INTEGERFLOAT(P)Date/Time: DATE, TIME, TIMESTAMPDECIMAL(W, R)BOOLEANRelationshipsAlternative TerminologyTable-oriented Set-oriented Record-orientedTable Relation Record-type, fileRow Tuple RecordColumn Attribute FieldRelational Model DefinitionsA relation is a table with columns and rows.An attribute is a named column of a relation.A tuple is a row of a relation.A domain is a set of allowable values for one or moreattributes.The degree of a relation is the number of attributes it contains.The cardinality of a relation is the number of tuples it contains.A relational database is a collection of normalized relations with distinct relation names.The intension of a relation is the structure of the relation including its domains.The extension of a relation is the set of tuples currently in the relation.ExampleTry It1) What is the name of the relation?2) What is the cardinality of the relation?3) What is the degree of the relation?4) What is the domain of StdGPA? What is the domain of StdSSN?StdSSN StdLastName StdMajor StdClass StdGPA 123-45-6789 WELLS IS FR 3.00 124-56-7890 NORBERT FIN JR 2.70 234-56-7890 KENDALL ACCT JR 3.50 StudentDatabaseA database consists of multiple relationsInformation about an enterprise is broken up into parts, with each relation storing one part of the informationaccount : stores information about accounts depositor : stores information about which customer owns which account customer : stores information about customersStoring all information as a single relation such as bank(account_number, balance, customer_name, ..)results inrepetition of information (e.g., two customers own an account)the need for null values (e.g., represent a customer without an account)Formal DefinitionThe relational model may be visualized as tables and fields, but it is formally defined in terms of sets and set operations.A relation schema R with attributes A =<A1, A2, …, An> is denoted R(A1, A2, …, An) where each Ai is an attribute name that ranges over a domain Di denoted dom(Ai).Example: Product(id, name, supplierId, categoryId, price)R = Product (relation name)Set A = {id, name, supplierId, categoryId, price}dom(price) is set of all possible positive currency valuesdom(name) is set of all possible strings that represent people's namesRelation Schemas and InstancesA relation schema is a definition of a single relation.The relation schema is the intension of the relation.A relational database schema is a set of relation schemas.A relation instance denoted r(R) over a relation schema R(A1, A2, …, An) is a set of n-tuples <d1, d2, ..., dn> where each di is an element of dom(Ai) or is null.The relation instance is the extension of the relation.A value of null represents a missing or unknown value.Cartesian Product (review)The Cartesian product written as D1 x D2 is a set operation that takes two sets D1 and D2 and returns the set of all ordered pairs such that the first element is a member of D1 and the second element is a member of D2.Example:D1 = {1,2,3}D2 = {A,B}D1 x D2 = {(1,A), (2,A), (3,A), (1,B), (2,B), (3,B)}Practice Questions: 1) Compute D2 x D1.2) Compute D2 x D2.3) If |D| denotes the number of elements in set D, how many elements are there in D1 x D2 in general.What is the cardinality of D1 x D2 x D1 x D1?Relation InstanceA relation instance r(R) can also be defined as a subset of the Cartesian product of the domains of all attributes in the relation schema. That is, r(R) dom(A1) x dom(A2) x … x dom(An)Example:R = Person(id, firstName, lastName)dom(id) = {1,2}, dom(firstName) = {Joe, Steve}dom(lastName) = {Jones, Perry}dom(id) x dom(firstName) x dom(lastName) = { (1,Joe,Jones), (1,Joe,Perry), (1,Steve,Jones), (1,Steve,Perry), (2,Joe,Jones), (2,Joe,Perry), (2,Steve,Jones), (2,Steve,Perry)}Properties of RelationsA relation has several properties:Each relation name is unique.No two relations have the same name.Each cell of the relation (value of a domain) contains exactly one atomic (single) value.Each attribute of a relation has a distinct name.The values of an attribute are all from the same domain.Each tuple is distinct. There are no duplicate tuples.This is because relations are sets. In SQL, relations are bags.The order of attributes is not important.The order of tuples has no significance.Relational KeysKeys are used to uniquely identify a tuple in a relation.Note that keys apply to the relational schema not to the relational instance. That is, looking at the current instance cannot tell you for sure if the set of attributes is a key.A superkey is a set of attributes that uniquely identifies a tuple in a relation.A key is a minimal set of attributes that uniquely identifies atuple in a relation.A candidate key is one of the possible keys of a relation.A primary key is the candidate key designated as the distinguishing key of a relation.A foreign key is a set of attributes in one relation referring to a candidate key of another relation.Many DDLs require that a foreign reference a primary key.Example


View Full Document

UNCA CSCI 343 - The Relational Model

Download The Relational Model
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 The Relational Model 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 The Relational Model 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?