INFO & DATABASE SYS 1
INFO & DATABASE SYS 1 CIS 4301
Popular in Course
Popular in Comm Sciences and Disorders
This 62 page Class Notes was uploaded by Aliyah Boyer on Friday September 18, 2015. The Class Notes belongs to CIS 4301 at University of Florida taught by Jermaine in Fall. Since its upload, it has received 15 views. For similar materials see /class/207029/cis-4301-university-of-florida in Comm Sciences and Disorders at University of Florida.
Reviews for INFO & DATABASE SYS 1
Report this Material
What is Karma?
Karma is the currency of StudySoup.
Date Created: 09/18/15
Entity Relationship Model Building a software that uses a DB is a 3step process 1 Model the part of the real world the DB will store 2 Use that model to obtain DB design 3 Implement the design First 2 weeks of class We will focus on rst part Modeling methodology we ll use ER Model ER Model Basics Goal Model the part of the real world the DB will store ER Model is a graphical pictorial model All the world is divided into two classes Entities people places things ideas Re1ationships interactions between entities ER Model Basics Say we want to model what s going on right now Or if we want to show that the Prof actually teaches a class Cardinalities ER Model has ability to show extent to which instances of entities participate in a relationship cardinalities N stands for many Read this as A mother has many children A child has one mother ManyToMany Relationships Last relationship was a onetomany Can have manytomany MN Note no good reason to use M and N could just use N and N except tradition Note 2 This example demonstrates importance of context if we are talking about just this classroom Teaches is onetomany at UF it is manytomany Other Types of Relationships Can also have onetoone but somewhat rare 11 Other cardinalities are possible 2 would mean an instance of an entity participates twice in the relationship 15 would mean the instance participates between 1 and 5 times Note difference between an entity and an instance of an entity Prof is an entity referring to the class of all professors When we do cardinalities we ask what is the extent to which an instance of an entity participates in a relationship Total Participation Requirement AKA Existence Dependencies What if every instance of an entity must participate in a rel n p EX Every class has to have a prof to teach it Denoted with a bold link MN Means every class must have a prof to teach it Can you think of other examples Attributes Every entity has to have one or more attributes Attributes are characteristics associated with an entity EX Attributes On Relationships HOW do we model the fact that a student gets a grade in a class With this Both are bad Why Attributes On Relationships cont Here is the correct solution Keys Every entity has one or more attributes that uniquely ID an instance Collectively these attributes are the key for the entity EX Weak Entities However some entities need to get some or all of their key from another entity known as a weak entity EX Say that Building was its own entity Weak Entities cont Of ce needs Building to get part of its key Is a weak entity Weak entity indicated With a bold line ia enti ing relationship is also in bold any partial key is denoted with a dashed underline To figure out the key for a weak entity start with all of the attributes in the partial key then chase the IDing relationsips A Related Question When is a person place thing idea an entity and when is it just an attribute First rule of thumb If the same attribute shows up in more than one place in your model make it an entity instead EX if a bunch of entities all have a building attribute Build ing should be an entity instead A Related Question cont Second rule of thumb If the attribute shows up in just one place but it is very complex and more than one instance of the entity that it is attached to can have the same attribute value make it an entity instead Ex Since we have a lot of info about a building its address age the number of rooms etc and more than one room is in the same building make it an entity A Related Question cont Third rule of thumb If an entity is related to only one other entity and it is very sim ple or the relationship is a onetoone then make it an attribute instead EX A prof s rank should not be an entity since it is simple just a word and it is not attached to any other entities Inheritance Say we have different types of people in our model students staff profs And they share a lot of characteristics Use inheritance to clean up the model So all people have an SSN Age Name and a home town but only Profs have a Sal ary and Rank D means that a Per son can only be a Stu dent a Staff or a Prof never more than one Inheritance cont If you use 0 for overlapping rather than D for distinct then an instance of the superclass can live in multiple subclasses at the same time EX In the part of the world we are modeling here a per son can be a student have a job andor be a parent pos sibly at the same time Employee Inheritance cont A bold line existence dep coming out of the superclass means that an instance of the superclass must also have a corresponding instance in the subclass If we are modeling a university this means that a Person has to be either a Student a Prof or on the Staff there are no generic People who are not in a sub class UnionType Superclasses Say you have a massive inheritance hierarchy And every entity in the hierarchy has two distinct subtypes Man and Woman To show this we could simply subclass every leaf node in the hierarchy into Man and Woman a big mess Or use the Union superclass UnionType Superclasses cont Man Woman This means that every instance of every class in the hierarchy will also be an instance of the class Man or Woman but not both How is this different than a distinct subclass End of Lecture One on ER Models High Degree Relationships Last new aspect of ER Modeling for us to discuss Motivating example Model that people go to the theatre and watch a movie Hard to model well with binary relationships Why Why Is This Solution Bad Problem each theatre shows many movies No way to know which movie someone saw when they went to the theatre Why won t this help Person We now know what movies the person has seen but we have no idea which theatre she saw them at Solution Create a new entity In our example introduce the idea of a Theatre Trip Now we know what movies the person saw and where Note exist dep on each of the three entities Theatre Trip is attached to Can t exist wo a person a theatre and some movies Theatre Trip is really a 3way relationship Can replace Theatre Trip with a 3way relationship Means the same thing but makes the diagrammodel a bit clearer Highdegree relationships are not standard in ER models no stan dard notation exists But you do see them Will use them in our class Person Person If there were an exist dep tween Person and Goes On then there would be an exist dep here Note that we simply drop the exist dep on Theatre Trip s side of each relationship since they always must be there Sample ER Modeling Problem Now we are ready to look at a single reasonably complex problem Take a look at the handout read it carefully Seems like a complicated model where in the heck will we start In general just pick some obvious entity and grow the model from there We will start with people since they seem central to the model Where to start Build the model piece by piece At a certain university there are two kinds of people students and professors Furthermore students and either be under graduate students or graduate students Person Dark lines since 1 Every person is a student or a prof 2 Every student is D since can t a grad or undergrad be both a student Ugrad Grad and a prof can t be both a ugrad and a grad student If could be both use a O F or every person we have a SSN which uniquely identifies the per son an address with street number city and state a phone number a sex and a data of birth All people have a date when they began at the university as well as a date when they left the uni versity SSN is the key note the underline Prof teaches many classes Students a lass taughft take classes and y one pro have either graduated or rzot Professors 03 teach classes and also have a rank and a sal w N ary NM Ugrad Grad Student takes many classe class taken by many students Graduate students can TA for a class Graduate stu dents are either PhD or MS stu dents This one is easy Graduate students are advised by one or more professors and are affiliated with a department which will grant their degree Every grad student is advised by a professor Dark line means that can t have a grad student who is not af liated with a department When a student takes a class they get a grade in that class A class is an instance of a course which is identified by a course number and the department that offers it N 3 5 M e quot 1 Grade is on Takes I ark diamond means it s an identifying relationship Ugrad Grad N M so course gets part of its key from ment Af l t a Dashed underline means a partial key full key is Num key of ment Dark line indicates Course is a weak entity A course has a number of prerequities which are other courses a number of credit hours and a name A class is iden tified by a section number the semester and the course that it is an instance of 3 w y E N required by X M N 1 req39 M V Ugrad Grad N N red fm N Prereq is a recursive relationship note labels to show the two roles in the relationship Af l t a Class is weak too since it needs its course to ID it Every department has to have a chair Bold line Every professor has an af liation a N Af l t M 1 4 Adres w Y w E N required by X M N a 1 M N Ugrad Grad N M 1required for Af l t a A department has a department chair who is also a professor a name and a address Professors are affiliated with one or more departments and each affiliation has a appointment e Undergraduate students have a major which is offered by a department and has a set of required classes that a student must take in order to graduate The stuff up here is cutoff for clarity 3 w y E N I required by X M N 1 Course I Prereq i i M Ugrad Grad N N M 1 N Note that our model shows a All students have a mai or b All maiors require at least one course c All majors are offered b me department A major has a name but different departments can offer majors with the same name you can get a CS major from the CISE department or from the business school these are not the same majors however Adres 3 w Y N required by X M N 1 Course I Prereq U 1 Grad N N M ra g i M 1 N Af l t 1 N Offers this means that Major is weak and requires ment for part of its key A major is associated with either a BS or a BA and has a minimum number of credit hours that are required to gradu ate These are simply two new attributes Adres Offere a by f I required by a 1 Course I Prereq N M And we are totally done 1 Teaches S 1 N a ary N Advises Grade E M N Takes M nsg nc 6 1 i Ugrad Grad N TAS C N B4 lass WOW Querying in the Relational Model Relational model is not all about storage Also allows data manipulation queries updates Queries in RM idea is to mathematically specify a new relation that holds only the answer tuples 39 Two ways to specify Relational Calculus and Rela tional Algebra Ways To Specify Queries Relational Calculus Related to predicate calculus rst order logic Discrete math strikes again Idea specify What the answer tuples look like Advantage relatively easy to specify the most complex queries Disadvantage far removed from implementation Other way Relational Algebra Ways To Specify Queries Cont d Relational Algebra Like writing a program Specify a list of operations that are performed on data stepby step It s an algebra over relations Advantage closely resembles how a database actually works Advantage simple queries easier to specify in RA compared to RC Disadvantage more dif cult queries are often very dif cult to specify in RA But RA and RC have same power in the end Why Not Go Straight to SQL SQL or structured query language is worldwide standard RDB query language Why not skip this RARC stuff Hard to really understand SQL Wo background in RARC esp RC Modern DB systems implement RA under the hood So hard to understand tuning DBA Wo grounding in RA We ll do RA rst Relational Algebra Any algebra needs a domain and a set of operations The domain for RA is all valid relations The RA consists of a set of unarybinary operations Like all algebras RA is closed Apply an op to a relation or a pair of valid relations Then you get back a valid relation Relational Selection Most fundamental operation is relational selection Is a simple lter over a relation s tuples Given a relation R selection is written as 63R In databases the Greek letter 6 is the selection operator Different from the SQL SELECT clause B is some boolean function acceptingrejecting single tuples from R Key rule can only look at single tuples Key rule no other outside information allowed Typical operations in B are 72 and or not etc Relational Selection 0 Example LIKES drinker beer If Joe Milwaukee 8 Best is in LIKES it means that Joe likes Milwau kee s Best Say we want all of the people who like the beer Bud Simply use Ghee BudLIKES Relational Selection cont d lf ms eOfLIKESdrinker beer Joe Bud Sam Bud Sue Coors John Beast 0 Then Ghee BudLIKES is Joe Bud Sam Bud Say we want all of the people who like either Bud 0r The Beast ah1 Milwaukee s Best Simply use Gbeer Budv beer BeaslLIKES Relational Projection However if we want the people who drink Bud weiser will cheer BudLIKES really do it Problem gives us the beer Bud in every tuple Projection kills unwanted attributes Given a relation R projection is written as nPR In databases the Greek letter TC is the projection operator P lists the attributes that you wish to retain Note changes the schema of the output relation Relational Projection cont d So ndrmkermbeer BudLIKES is What we really want lf ms eOfLIKESdrinker beer Joe Bud Sam Bud Sue Coors John Beast Then ndrmkermbeer BudLIKES gives us a one attribute relation attribute name dr i n ke r and the mpbsJoe Sam The Various Join Operators In relational model the answer to a query is often spread across multiple relations Can be put together Via the various join operators All of these are based upon the cross product opera tion denoted by a gtlt Given R and S then R x S returns the following result For r in R For sin S result result U r 0 s Cross Product SayuwlmveLIKESandSERVES bar beer IfMoe s BudiSHISERVES n mnS Moe 3 serves Bud 39Thes eOfLIKESdrinker beer Joe Bud Sam Bud Sue Coors John Beast 39ThC MBOfSERVES bar beerm Moe s Bud Moe s Beast Thm1LHQESxSERVESiampH Cross Product cont d Joe Bud Sam Bud Sue Coors John Beast x Moe s Bud Moe s Beast Joe Bud Moe s Bud Sam Bud Moe s Bud Sue Coors Moe s Bud John Beast Moe s Bud Joe Bud Moe s Beast Sam Bud Moe s Beast Sue Coors Moe s Beast John Beast Moe s Beast Cross Product con t So now what if we want all people who can get a beer that they like at Moe s Can use TEMPdrinkr bl bar 92 lt LIKES gtlt Gbar ZMoeSSER VES For convenience this speci es a temporary relation to hold the result of the cross product Followed by ndrmkr6bl The Join Operator All of the time we end up doing cross product fol lowed by selection to link across a foreign key Can use the join operator as shorthand RNBS This does a cross product over R and S then applies a relational selection using the predicate B Ignoring the projection the last query could be 39 HKESWLBAR SBARGbar MoeSSER VESD Note convention is to use the dot notation to dis ambiguate attribute names in join predicate The Natural Join Operator Most of the time the join is a socalled equijoin over all attributes having the same name followed by deletion of duplicate attributes 39 Can use the natural join Operator as shorthand for this R S The Natural Join Operator cont d 0 Example LIKES obar Z M06SSER VES 0 This nds all attributes in LIKES and SERVES that have the same name LIKES beer and SERVESbeer Then does an equij oin on them Then removes the redundant attributes resulting in the output schema drinker beer bar So all people who can get a beer they like at Moe s 1s TcdrmkerLIKES Gbar M06SSER The Natural Join Operator cont d A bigger example Say we have Ra b C d e f Sd e f g h i Then R S does a join with the predicate Rd SdRe SeRf Sf And gives the output schema a b c d e f g h i Set Operations RA also includes the standard set operations Subtraction R S is all tuples in R but not in S Union R U S is all tuples in R or in S Intersection R m S is all tuples in R and in S Note to be applicable R and S must have the same schema Sometimes the rename operation is useful pPR This takes R and changes the names to those in P So LIKES p drm key barSER VES is a valid but really strange RA expression Set Operations cont d EX say we want people who like Bud or The Beast aka Milwaukee 8 Best but not both ndrmker6beer ndrmker6beer Beast ANSWER BUD UBEAST BUD BEAST Next Week We will do a set of inclass problems that will give examples of how to write more complex RA queries