CSC 3800 Database Management SystemsCourse OverviewContent of CSC 3800Do You Know SQL?And How About These?Course RequirementsInteresting Stuff About DatabasesMore Interesting StuffStill More …And More…What is a Data Model?A Relation is a TableSchemasWhy Relations?Our Running ExampleDatabase Schemas in SQLCreating (Declaring) a RelationElements of Table DeclarationsExample: Create TableSQL ValuesDates and TimesTimes as ValuesDeclaring KeysDeclaring Single-Attribute KeysDeclaring Multiattribute KeysExample: Multiattribute KeyPRIMARY KEY vs. UNIQUESemistructured DataGraphs of Semistructured DataExample: Data GraphXMLXML DocumentsTagsExample: an XML DocumentAttributesBars, Using AttributesDTD’s (Document Type Definitions)Example: DTDSlide 39Example: Attributes1CSC 3800Database Management SystemsChapter 1Fall 2008Dr. Chuck Lillie2Course OverviewCourse Informationwww.uncp.edu/home/lilliec/ SyllabusAssignmentsHomeworkExamsAttendance PolicyTextbookUllman and Widom, A First Course in Database Sysems, 3rd Edition, 20083Content of CSC 3800Design of databases.E/R model, relational model, semistructured model, XML, UML, ODL.Database programming.SQL, XPath, XQuery, Relational algebra, Datalog.Not DBMS implementation4Do You Know SQL?Explain the difference between:SELECT bFROM RWHERE a<10 OR a>=10;andSELECT bFROM R;a b5 2010 3020 40… … R5And How About These?SELECT aFROM R, SWHERE R.b = S.b;SELECT aFROM RWHERE b IN (SELECT b FROM S);6Course Requirements1. Project: a project supported by a database.Individual.Uses WAMPS.2. Homeworks: Problems from textbook3. Midterm and final.7Interesting Stuff About DatabasesIt used to be about boring stuff: employee records, bank records, etc.Today, the field covers all the largest sources of data, with many new ideas.Web search.Data mining.Scientific and medical databases.Integrating information.8More Interesting StuffDatabase programming centers around limited programming languages.Only area where non-Turing-complete languages make sense.Leads to very succinct programming, but also to unique query-optimization problems9Still More …You may not notice it, but databases are behind almost everything you do on the Web.Google searches.Queries at Amazon, eBay, etc.10And More…Databases often have unique concurrency-control problemsMany activities (transactions) at the database at all times.Must not confuse actions, e.g., two withdrawals from the same account must each debit the account.11What is a Data Model?1. Mathematical representation of data.Examples: relational model = tables; semistructured model = trees/graphs.2. Operations on data.3. Constraints.12A Relation is a Table name manfWinterbrew Pete’sBud Lite Anheuser-BuschBeersAttributes(columnheaders)Tuples(rows)Relation name13SchemasRelation schema = relation name and attribute list.Optionally: types of attributes.Example: Beers(name, manf) or Beers(name: string, manf: string)Database = collection of relations.Database schema = set of all relation schemas in the database.14Why Relations?Very simple model.Often matches how we think about data.Abstract model that underlies SQL, the most important database language today.15Our Running ExampleBeers(name, manf)Bars(name, addr, license)Drinkers(name, addr, phone)Likes(drinker, beer)Sells(bar, beer, price)Frequents(drinker, bar)Underline = key (tuples cannot have the same value in all key attributes).Excellent example of a constraint.16Database Schemas in SQLSQL is primarily a query language, for getting information from a database.But SQL also includes a data-definition component for describing database schemas.17Creating (Declaring) a RelationSimplest form is:CREATE TABLE <name> (<list of elements>);To delete a relation:DROP TABLE <name>;18Elements of Table DeclarationsMost basic element: an attribute and its type.The most common types are:INT or INTEGER (synonyms).REAL or FLOAT (synonyms).CHAR(n ) = fixed-length string of n characters.VARCHAR(n ) = variable-length string of up to n characters.19Example: Create TableCREATE TABLE Sells (bar CHAR(20),beer VARCHAR(20),price REAL);20SQL ValuesIntegers and reals are represented as you would expect.Strings are too, except they require single quotes.Two single quotes = real quote, e.g., ’Joe’’s Bar’.Any value can be NULL.Except key value21Dates and TimesDATE and TIME are types in SQL.The form of a date value is:DATE ’yyyy-mm-dd’Example: DATE ’2007-09-30’ for Sept. 30, 2007.22Times as ValuesThe form of a time value is:TIME ’hh:mm:ss’with an optional decimal point and fractions of a second following.Example: TIME ’15:30:02.5’ = two and a half seconds after 3:30PM.23Declaring KeysAn attribute or list of attributes may be declared PRIMARY KEY or UNIQUE.Either says that no two tuples of the relation may agree in all the attribute(s) on the list.There are a few distinctions to be mentioned later.24Declaring Single-Attribute KeysPlace PRIMARY KEY or UNIQUE after the type in the declaration of the attribute.Example:CREATE TABLE Beers (name CHAR(20) UNIQUE,manf CHAR(20));25Declaring Multiattribute KeysA key declaration can also be another element in the list of elements of a CREATE TABLE statement.This form is essential if the key consists of more than one attribute.May be used even for one-attribute keys.26Example: Multiattribute KeyThe bar and beer together are the key for Sells:CREATE TABLE Sells (bar CHAR(20),beer VARCHAR(20),price REAL,PRIMARY KEY (bar, beer));27PRIMARY KEY vs. UNIQUE1. 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.28Semistructured DataAnother data model, based on trees.Motivation: flexible representation of data.Motivation: sharing of documents among systems and databases.29Graphs of Semistructured DataNodes = objects.Labels on arcs (like attribute names).Atomic values at leaf nodes (nodes with no arcs out).Flexibility: no restriction on:Labels out of a node.Number of successors with a given label.30Example: Data GraphBudA.B.Gold1995MapleJoe’sM’lobbeer beerbarmanfmanfservedAtnamenamenameaddrprizeyear awardrootThe bar objectfor