Relational Data Model CS 157B

1. 1 Relational Data ModelCS 157B Nidhi Patel

2. 2 What is a Data Model? A notation for describing data or information Description consists of 3 parts: 1) Structure of the data -Arrays and structures or objects 2) Operations on the data -Queries (Operations that retrieve information) -Modifications (Operations that change the database) 3) Constraints on the data -Limitations

3. 3 Important Data Models 1) The relational model (including object-relational extensions) -Present in all commercial database management systems 2) The semistructured-data model (including XML and related standards)

4. 4 The Relational Model Based on tables The structure portion resemble an array of structs in C Column headers ?Field names Each row ?values of struct in the array

5. 5 An example relation

6. 6 The Semistructured Model Semistructured data resembles trees or graphs Represent data by hierarchically nested tagged elements The tags are similar to those used in HTML Tags define the role played by different pieces of data

7. 7 Movie data as XML <Movies> <Movie title=�Gone With the Wind�> <Year>1939</Year> <Length>231</Length> <Genre>drama</Genre> </Movies> <Movie title=�Star Wars�> <Year>1977</Year> <Length>124</Length> <Genre>sciFi</Genre> </Movies> <Movie title=�Wayne�s World�> <Year>1992</Year> <Length>95</Length> <Genre>comedy</Genre> </Movies> </Movies>

8. 8 Other Data Models Object-oriented features -Values can have structure -Relations can have associated methods Object-relational model --- which are analogous to the way structs in C were extended to objects in C++

9. 9 Comparison of Modeling Approaches Semistructured models have more flexibility than relations Nevertheless, the relational model is still preferred Because database are large, -efficiency of access to data -efficiency of modifications to data -ease of use These all goals can be achieved with a model, particularly the relational model

10. 10 Comparison of Modeling Approaches Relational model provides - A simple, limited approach to structuring data, yet is reasonably flexible so anything can be modeled - A limited, yet useful, collection of operations on data

11. 11 Basics of the Relational Model Represent data as a two-dimensional table called a relation

12. 12 Basics of the Relational Model Domain: a particular elementary type For example: integer, string Movies (title:string, year:integer, length:integer, genre:string)

13. 13 Keys of Relations A set of attributes forms a key for a relation, if we don�t allow two tuples in a relation instance to have the same values in all the attributes of the key Relation Movies has a key consisting of the two attributes title and year Attribute or attributes that form a key for a relation by underlining the key attribute(s). Movies (title, year, length, genre)

14. 14 Defining a Relation Schema in SQL SQL (pronounced �sequel�) is the principal language used to describe and manipulate relational databases Two aspects to SQL 1) Data-Definition ? for declaring database schemas 2) Data-Manipulation ? for querying and modifying database

15. 15 Relations in SQL SQL makes difference between 3 kinds of relations: - Stored relations ? tables - Views - Temporary tables

16. 16 Data Types All attributes must have a data type 1) Character strings of fixed or varying length CHAR(n) ? a fixed-length string of up to n characters OR VARCHAR (n)

17. 17 Data Types 2) Bit strings of fixed or varying length BIT(n) ?bit strings of length n BIT VARYING(n) ? bit strings of length up to n 3) The type BOOLEAN denotes an attribute whose value is logical - The possible values are TRUE, FALSE, and UNKNOWN

18. 18 Data Types 4) The type INT or INTEGER denotes typical integer values - The type SHORTINT also denotes integers, but the number of bits permitted may be less depending on the implementation 5) The type FLOAT denotes floating-point numbers - Real numbers with a fixed decimal point - DECIMAL (n,d) allows values that consist of n decimal digits, with the decimal point assumed to be d positions from the right

19. 19 Data Types 6) Dates and times can be represented by the data types DATE and TIME respectively

20. 20 Simple Table Declarations CREATE TABLE Movies ( title CHAR(100), year INT, length INT, genre CHAR(10), studioName CHAR(30), producerC# INT );

21. 21 Modifying Relation Schemas ALTER TABLE MovieStar ADD phone CHAR(16); ALTER TABLE MovieStar DROP birthdate;

22. 22 Default Values gender CHAR(1) DEFAULT �?�, birthdate DATE DEFAULT DATE �0000-00-00� ALTER TABLE MovieStar ADD phone CHAR(16) DEFAULT �unlisted�;

23. 23 Declaring Primary Keys CREATE TABLE MovieStar ( name CHAR(30), address VARCHAR(255), gender CHAR(1), birthdate DATE, PRIMARY KEY (name) );

24. 24 Declaring Foreign Keys Example: Suppose we wish to declare the relation Studio (name, address, presC#) Whose primary key is name and which has a foreign key presC# that references cert# of relation MovieExec (name, address, cert#, netWorth) Solution: CREATE TABLE Studio ( name CHAR(30) PRIMARY KEY, address VARCHAR(255), presC# INT, FOREIGN KEY (presC#) REFERENCES MovieExec (cert#)

25. 25 Adding and Deleting Tuples Insert a single tuple using: INSERT INTO StarsIn VALUES (�The Maltese Falcon�, 1942, �Sydney Greenstreet�); Delete all tuples satisfying some condition: DELETE FROM StarsIn WHERE movieTitle = �The Maltese Falcon� AND movie Year = 1942 AND starName = �Sydney Greenstreet�;

26. 26 Relational Data Model It is almost impossible to store a huge amount of data without proper manage. To manage and store data, many methods and models have been developed. Relational Database Model, which has proved to be the best data management model.

27. 27 Invented by Relational data model was invented by Edgar F. Codd Subsequently maintained and developed by Chris Date and Hugh Darwen among others The relational data model is based on the predicate logic and set theory of mathematics. Codd used mathematical n-ary relations as a base to represent data, which is a subset of the Cartesian product of n sets.