Database week 1-6.pdf

CITS1402 Relational Database Management Systems Week 1—Introduction Dr Mehwish Nasim/ CSSE Sem 2/2024 [email protected] Databases 2...

CITS1402 Relational Database Management Systems Week 1—Introduction Dr Mehwish Nasim/ CSSE Sem 2/2024 [email protected] Databases 2 Aims Familiarise students with the basic concepts, fundamental structures and general techniques that are needed to define, construct and manipulate a database. Using a database management system (DBMS) with the main emphasis on relational DBMSs. Understand the concept of data models, have practical skills in data modelling using the Entity-Relationship (ER) and Extend Entity-Relationship (EER) Relational data models, Diagrammatic representations, Normal Forms Relational Algebra and Structured Query Language (SQL). In addition, students will begin to explore concepts associated with data mining and knowledge discovery. Learning Outcomes 01 02 03 04 05 understand existing understand the refine the database correctly program build an application database 'business' to improve and structured query layer interface for implementation and requirements to ensure correctness language (SQL) easier user create refinements design a database and reliability queries and reports interaction and improvements through analysis 5 Software SQLite version 3.x https://sqlite.org/ “SQLite is a relational database management system contained in a C programming library. In contrast to other database management systems, SQLite is not implemented as a separate process that a client program running in another process accesses. Rather, it is part of the using program. Command Line Shell for SQLite http://www.sqlite.org/cli.html Objectives Common uses of database systems Traditional File-Based Systems Database Approach Roles in the Database Environment History of Database Management Systems Advantages and Disadvantages of DMBSs 7 described a database table (columns) to represent the Lease data described a database table (columns) to represent the Lease data described a database table (columns) to represent the Lease data Examples of Database Applications Purchases from Purchases using Booking a Using the local Taking out Renting a video Using the Internet Studying at the supermarket your credit card holiday at the library insurance university travel agents 13 Objectives Common uses of database systems Traditional File-Based Systems Database Approach Roles in the Database Environment History of Database Management Systems Advantages and Disadvantages of DMBSs File-Based Processing Pearson Education © 2009 File-Based Systems Collection of application programs Each program that perform services defines and manages for the end users (e.g. its own data. reports). 16 Limitations of File-Based Approach Separation and Each program maintains its own set of data. Users of one program may be unaware of isolation of data potentially useful data held by other programs. Duplication of Same data is held by different programs. Wasted space and potentially different values data and/or different formats for the same item. 17 Limitations of File-Based Approach Data dependence Incompatible file formats Fixed Queries/Proliferation of application programs File structure is defined in the program Programs are written in different Programs are written to satisfy particular code. languages, and so cannot easily access functions. each other’s files. Any new requirement needs a new program. 18 Objectives Common uses of database systems Traditional File-Based Systems Database Approach Roles in the Database Environment History of Database Management Systems Advantages and Disadvantages of DMBSs Database Approach Arose because: Definition of data was embedded in application programs, rather than being stored separately and independently. No control over access and manipulation of data beyond that imposed by application programs. Result: the database; and Database Management System (DBMS). Database Shared collection of logically related data (and a description of this data), designed to meet the information needs of an organization. System catalog (metadata) provides description of data to enable program–data independence. Logically related data comprises entities, attributes, and relationships of an organization’s information. Database Management System (DBMS) A software system that enables users to define, create, maintain, and control access to the database. (Database) application program: a computer program that interacts with the database by issuing an appropriate request (SQL statement) to the DBMS. File-Based Processing Database Management System (DBMS) Database Approach Permits specification of data types, structures Data definition and any data constraints. language (DDL). All specifications are stored in the database. Data General enquiry facility (query language) of manipulation the data. language (DML). 25 Database Approach Controlled access to database may include: a security system an integrity system a concurrency control system a recovery control system a user-accessible catalog. Views Allows each user to have his or her own view of the database. A view is essentially some subset of the database. Views External Conceptual View Views - Benefits Reduce complexity Provide a level of security Present a consistent, Provide a mechanism to unchanging picture of the customize the appearance structure of the database, of the database even if the underlying database is changed 29 Objectives Common uses of database systems Traditional File-Based Systems Database Approach Roles in the Database Environment History of Database Management Systems Advantages and Disadvantages of DMBSs Roles in the Database Environment Data Administrator (DA) Database Administrator (DBA) Database Designers (Logical and Physical) Application Programmers End Users (naive and sophisticated) 31 Objectives Common uses of database systems Traditional File-Based Systems Database Approach Roles in the Database Environment History of Database Management Systems Advantages and Disadvantages of DMBSs History of Database Systems First-generation, Apollo Space Mission 1960s Hierarchical and Network Models Second Relational Models E.F. Codd, “A relational model of data for large shared data banks” generation, 1970s SQL developed Third generation Object-Relational Object-Oriented 33 Objectives Common uses of database systems Traditional File-Based Systems Database Approach Roles in the Database Environment History of Database Management Systems Advantages and Disadvantages of DMBSs Advantages of DBMSs More Control of data Data information from Sharing of data redundancy consistency the same amount of data Improved data Improved Enforcement of Economy of integrity security standards scale 35 Advantages of DBMSs Improved data Balance conflicting Increased accessibility and requirements productivity responsiveness Improved Improved backup Increased maintenance through and recovery concurrency data independence services 36 Disadvantages of DBMSs Complexity Size Cost of DBMS Additional Cost of Performance Higher impact hardware costs conversion of a failure 37 Objectives Common uses of database systems Traditional File-Based Systems Database Approach Roles in the Database Environment History of Database Management Systems Advantages and Disadvantages of DMBSs  CITS1402 Relational Database Management Systems Week 2—Relational Model and Relational Algebra Dr Mehwish Nasim/ CSSE Sem1/2024 [email protected]  Contents Introduction Week1 Databases CITS1402 Relational Model How to Program Show the terminal  Are these rows/records unique? Yes. But why? Does the order matter? Can staffNo be NULL? Why are the column names unique? Does the order mater? What values can “salary” have? select fname, lname, city How do we “know” this query from Staff Natural Join Branch will return all records? where position = ‘Manager’ Agenda ü Connection between mathematical relations and relations in the relational model. ü How tables are used to represent data. ü Properties of database relations. ü Terminology of relational model. ü How to identify CK, PK, and FKs. ü Meaning of entity integrity and referential integrity. Agenda ü Connection between mathematical relations and relations in the relational model. ü How tables are used to represent data. ü Properties of database relations. ü Terminology of relational model. ü How to identify CK, PK, and FKs. ü Meaning of entity integrity and referential integrity. Mathematical Definition of Relation Consider two sets, D1 & D2, where D1 = {2, 4} and D2 = {1, 3, 5}. Cartesian Product: D1 ´ D2 set of all ordered pairs, first element is member of D1 and second element is member of D2. D1 ´ D2 = {(2, 1), (2, 3), (2, 5), (4, 1), (4, 3), (4, 5)} Mathematical Definition of Relation  Any subset of Cartesian product is a relation, R; D1 ´ D2 = {(2, 1), (2, 3), (2, 5), (4, 1), (4, 3), (4, 5)} R = {(2, 1), (4, 1)} May specify which pairs are in relation using some condition for selection; e.g. second element is 1: R = {(x, y) | x ÎD1, y ÎD2, and y = 1} first element is always twice the second: S = {(x, y) | x ÎD1, y ÎD2, and x = 2y} S = {(2, 1)} Mathematical Definition of Relation Consider three sets D1, D2, D3 with Cartesian Product D1 ´ D2 ´ D3; e.g. D1 = {1, 3} D2 = {2, 4} D3 = {5, 6} D1 ´ D2 ´ D3 = {(1,2,5), (1,2,6), (1,4,5), (1,4,6), (3,2,5), (3,2,6), (3,4,5), (3,4,6)} Any subset of these ordered triples is a relation. Mathematical Definition of Relation Cartesian product of n sets (D1, D2,..., Dn) is: D1 ´ D2 ´... ´ Dn = {(d1, d2,... , dn) | d1 ÎD1, d2 ÎD2,... , dnÎDn} n usually written as: Π i=I Di Any set of n-tuples from this Cartesian Product is a relation on the n sets. Agenda ü Connection between mathematical relations and relations in the relational model. ü How tables are used to represent data. ü Properties of database relations. ü Terminology of relational model. ü How to identify CK, PK, and FKs. ü Meaning of entity integrity and referential integrity. So…how does this apply to a database? So…how does this apply to a database? Let A1, A2,…An, be attributes (columns) with domains (possible values) D1, D2,…Dn The set {A1:D1, A2:D2,…An:Dn} is a relation schema Relation R is the mapping from attribute to domain (A1:d1, A2:d2,…An:dn) We can think of a relation in the relational model as any subset of the Cartesian Product of the domains of the attributes How does this apply to a database? The set {A1:D1, A2:D2,…An:Dn} is a relation schema {branchNo: BranchNumbers, street: StreetNumbers, city: CityNames, postcode: Postcodes} How does this apply to a database? Relation R is the mapping from attribute to domain (A1:d1, A2:d2,…An:dn) {(branchNo: B005, street: 22 Deer Rd city: London, postcode: SW1 4EH)} A relation instance A table is a physical representation of a relation Database Relations Relation schema Named relation defined by a set of attribute and domain name pairs. Relational database schema Set of relation schemas, each with a distinct name. R = {R1, R2,… Rm} DreamHomeSchema = {Staff, Branch, …} Agenda ü Connection between mathematical relations and relations in the relational model. ü How tables are used to represent data. ü Properties of database relations. ü Terminology of relational model. ü How to identify CK, PK, and FKs. ü Meaning of entity integrity and referential integrity. Properties of Relations Relation name is distinct from all other relation names in relational schema. Each cell of relation contains exactly one atomic (single) value. Each attribute has a distinct name. Values of an attribute are all from the same domain. Properties of Relations Each tuple is Order of attributes distinct; there are has no significance. no duplicate tuples. Order of tuples has Why is this so no significance, exciting? theoretically. 19  Properties of Relations Each tuple is distinct; there are no duplicate tuples. Order of attributes has no significance. Order of tuples has no significance, theoretically. Why is this so exciting? 20 We can apply set operations to relations Agenda ü Connection between mathematical relations and relations in the relational model. ü How tables are used to represent data. ü Properties of database relations. ü Terminology of relational model. ü How to identify CK, PK, and FKs. ü Meaning of entity integrity and referential integrity. Relational Model Terminology A relation is a table with columns and rows. Only applies to logical structure of the database, not the physical structure. Attribute is a named column of a relation. Domain is the set of allowable values for one or more attributes. Examples of Attribute Domains Relational Model Terminology Tuple is a row of a relation. Degree is the number of attributes in a relation. Cardinality is the number of tuples in a relation. Relational Database is a collection of normalized relations with distinct relation names. Instances of Branch and Staff Relations Relational Model Terminology The structure of a relation, and a Remains fixed specification of the domains is called the e.g. Branch (branchNo, street, city, postcode) intension The tuples/rows are Changes over time called the extension of a relation e.g. (B005,22 Deer Rd, London, SW1 4EH) 26 Alternative Terminology for Relational Model Agenda ü Connection between mathematical relations and relations in the relational model. ü How tables are used to represent data. ü Properties of database relations. ü Terminology of relational model. ü How to identify CK, PK, and FKs. ü Meaning of entity integrity and referential integrity. Relational Keys Superkey An attribute, or set of attributes, that uniquely identifies a tuple within a relation. Candidate Superkey (K) such that no proper subset is a superkey within the relation. In each tuple of R, values of K uniquely identify that tuple (uniqueness). Key No proper subset of K has the uniqueness property (irreducibility). 29 Relational Keys Relational Keys Primary Key Candidate key selected to identify tuples uniquely within relation. Alternate Keys Candidate keys that are not selected to be primary key. Foreign Key Attribute, or set of attributes, within one relation that matches candidate key of some (possibly same) relation. 31 Instances of Branch and Staff Relations Agenda ü Connection between mathematical relations and relations in the relational model. ü How tables are used to represent data. ü Properties of database relations. ü Terminology of relational model. ü How to identify CK, PK, and FKs. ü Meaning of entity integrity and referential integrity. Integrity Constraints - NULL Represents value for an attribute that is currently unknown or not applicable for tuple. Deals with incomplete or exceptional data. Represents the absence of a value is not the same as zero or spaces, which are values. Relational Keys Integrity Constraints Entity Integrity In a base relation, no attribute of a primary key can be null. Referential Integrity If foreign key exists in a relation, either foreign key value must match a candidate key value of some tuple in its home relation or foreign key value must be wholly null. 36 Relational Keys Relational Keys Integrity Constraints General Constraints Additional rules specified by users or database administrators that define or constrain some aspect of the enterprise. Client cannot view the property on the same day!  Are these rows/records unique? Yes. But why? Does the order matter? Can staffNo be NULL? Why are the column names unique? Does the order mater? What values can “salary” have? select fname, lname, city How do we “know” this query from Staff Natural Join Branch will return all records? where position = ‘Manager’ Agenda ü Connection between mathematical relations and relations in the relational model. ü How tables are used to represent data. ü Properties of database relations. ü Terminology of relational model. ü How to identify CK, PK, and FKs. ü Meaning of entity integrity and referential integrity.  CITS1402 Relational Database Management Systems Week 3—Relational Algebra Dr Mehwish Nasim/ CSSE Sem1/2024 [email protected]  Contents Relational Algebra Week3 Meaning of the term CITS1402 relational completeness. How to form queries in relational algebra. Objectives Meaning of the term relational completeness. How to form queries in relational algebra. How to form queries in tuple relational calculus. How to form queries in domain relational calculus. Categories of relational DML. Introduction Relational algebra and relational calculus are formal languages associated with the relational model. Informally, relational algebra is a (high-level) procedural language and relational calculus a non- procedural language. However, formally both are equivalent to one another. A language that produces a relation that can be derived using relational calculus is relationally complete. Chapter 5 - Objectives Meaning of the term relational completeness. How to form queries in relational algebra. How to form queries in tuple relational calculus. How to form queries in domain relational calculus. Categories of relational DML. Relational Algebra Relational algebra operations work on one or more relations to define another relation without changing the original relations. Both operands and results are relations, so output from one operation can become input to another operation. Allows expressions to be nested, just as in arithmetic. This property is called closure. Relational Algebra Selection, s Projection, P Cartesian product, X Union and Set Difference È, - These perform most of the data retrieval operations needed. Also have Join, Intersection, and Division operations, which can be expressed in terms of 5 basic operations. Relational Algebra Operations Relational Algebra Operations Selection (or Restriction) spredicate (R) Works on a single relation R and defines a relation that contains only those tuples (rows) of R that satisfy the specified condition (predicate). Example - Selection (or Restriction) List all staff with a salary greater than 10,000. DreamHome Database Branch (branchNo, street, city, postcode) Staff (staffNo, fName, lName, position, sex, DOB, salary, branchNo) PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo) Client (clientNo, fName, lName, telNo, prefType, maxRent, email) PrivateOwner (ownerNo, fName, lName, address, telNo, email, password) Viewing (clientNo, propertyNo, viewDate, comment) Registration (clientNo, branchNo, staffNo, dateJoined) Example - Selection (or Restriction) List all staff with a salary greater than £10,000. ssalary > 10000 (Staff) Projection Pcol1,... , coln(R) Works on a single relation R and defines a relation that contains a vertical subset of R, extracting the values of specified attributes and eliminating duplicates. Example - Projection Produce a list of salaries for all staff, showing only staffNo, fName, lName, and salary details. Example - Projection Produce a list of salaries for all staff, showing only staffNo, fName, lName, and salary details. PstaffNo, fName, lName, salary(Staff) Union RÈS Union of two relations R and S defines a relation that contains all the tuples of R, or S, or both R and S, duplicate tuples being eliminated. R and S must be union-compatible. If R and S have I and J tuples, respectively, union is obtained by concatenating them into one relation with a maximum of (I + J) tuples. Example - Union List all cities where there is either a branch office or a property for rent. DreamHome Database Branch (branchNo, street, city, postcode) Staff (staffNo, fName, lName, position, sex, DOB, salary, branchNo) PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo) Client (clientNo, fName, lName, telNo, prefType, maxRent, email) PrivateOwner (ownerNo, fName, lName, address, telNo, email, password) Viewing (clientNo, propertyNo, viewDate, comment) Registration (clientNo, branchNo, staffNo, dateJoined) Example - Union List all cities where there is either a branch office or a property for rent. Pcity(Branch) È Pcity(PropertyForRent) Set Difference R–S Defines a relation consisting of the tuples that are in relation R, but not in S. R and S must be union-compatible. Example - Set Difference List all cities where there is a branch office but no properties for rent. Example - Set Difference List all cities where there is a branch office but no properties for rent. Pcity(Branch) – Pcity(PropertyForRent) Intersection RÇS Defines a relation consisting of the set of all tuples that are in both R and S. R and S must be union-compatible. Expressed using basic operations: R Ç S = R – (R – S) Example - Intersection List all cities where there is both a branch office and at least one property for rent. Example - Intersection List all cities where there is both a branch office and at least one property for rent. Pcity(Branch) Ç Pcity(PropertyForRent) Cartesian product RXS Defines a relation that is the concatenation of every tuple of relation R with every tuple of relation S. Example - Cartesian product List the names and comments of all clients who have viewed a property for rent. DreamHome Database Branch (branchNo, street, city, postcode) Staff (staffNo, fName, lName, position, sex, DOB, salary, branchNo) PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo) Client (clientNo, fName, lName, telNo, prefType, maxRent, email) PrivateOwner (ownerNo, fName, lName, address, telNo, email, password) Viewing (clientNo, propertyNo, viewDate, comment) Registration (clientNo, branchNo, staffNo, dateJoined) Example - Cartesian product List the names and comments of all clients who have viewed a property for rent. Example - Cartesian product List the names and comments of all clients who have viewed a property for rent. (PclientNo, fName, lName(Client)) X (PclientNo, propertyNo, comment (Viewing)) Cartesian product and Selection Use selection operation to extract those tuples where Client.clientNo = Viewing.clientNo. Cartesian product and Selection Use selection operation to extract those tuples where Client.clientNo = Viewing.clientNo. sClient.clientNo = Viewing.clientNo((ÕclientNo, fName, lName(Client)) C (ÕclientNo, propertyNo, comment(Viewing))) Cartesian product and Selection can be reduced to a single operation called a Join. Join Operations Join is a derivative of Cartesian product. Equivalent to performing a Selection, using join predicate as selection formula, over Cartesian product of the two operand relations. One of the most difficult operations to implement efficiently in an RDBMS and one reason why RDBMSs have intrinsic performance problems. Join Operations Various forms of join operation Theta join Equijoin (a particular type of Theta join) Natural join Outer join Semijoin Theta join (q-join) R FS Defines a relation that contains tuples satisfying the predicate F from the Cartesian product of R and S. The predicate F is of the form R.ai q S.bi where q may be one of the comparison operators , ³, =, ¹. Theta join (q-join) Can rewrite Theta join using basic Selection and Cartesian product operations. R FS = sF(R C S) Degree of a Theta join is sum of degrees of the operand relations R and S. If predicate F contains only equality (=), the term Equijoin is used. Example - Equijoin List the names and comments of all clients who have viewed a property for rent. Example - Equijoin List the names and comments of all clients who have viewed a property for rent. (PclientNo, fName, lName(Client)) Client.clientNo = Viewing.clientNo (PclientNo, propertyNo, comment(Viewing)) Natural join R S An Equijoin of the two relations R and S over all common attributes x. One occurrence of each common attribute is eliminated from the result. Example - Natural join List the names and comments of all clients who have viewed a property for rent. Example - Natural join List the names and comments of all clients who have viewed a property for rent. (PclientNo, fName, lName(Client)) (PclientNo, propertyNo, comment(Viewing)) Outer join To display rows in the result that do not have matching values in the join column, use Outer join. R S (Left) outer join is join in which tuples from R that do not have matching values in common columns of S are also included in result relation. Example - Left Outer join Produce a status report on property viewings. Example - Left Outer join Produce a status report on property viewings. PpropertyNo, street, city(PropertyForRent) Viewing Semijoin R FS Defines a relation that contains the tuples of R that participate in the join of R with S. Can rewrite Semijoin using Projection and Join: R FS = PA(R F S) where “A” are only attributes from R Example - Semijoin List complete details of all staff who work at the branch in Glasgow. Example - Semijoin List complete details of all staff who work at the branch in Glasgow. Staff Staff.branchNo=Branch.branchNo(s city=‘Glasgow’(Branch)) Division R÷S Defines a relation over the attributes C that consists of set of tuples from R that match combination of every tuple in S. C = A – B, where C is the set attributes of R that are not attributes of S Good for “all” type queries Expressed using basic operations: T1 ¬ PC(R) T2 ¬ PC((S X T1) – R) T ¬ T1 – T2 Example - Division Identify all clients who have viewed all properties with three rooms. (PclientNo, propertyNo(Viewing)) ÷ (PpropertyNo(srooms = 3 (PropertyForRent))) Aggregate Operations ÁAL(R) Applies aggregate function list, AL, to R to define a relation over the aggregate list. AL contains one or more (, ) pairs. Main aggregate functions are: COUNT, SUM, AVG, MIN, and MAX. Example – Aggregate Operations How many properties cost more than 350 per month to rent? Example – Aggregate Operations How many properties cost more than £350 per month to rent? rR(myCount) ÁCOUNT propertyNo (σrent > 350 (PropertyForRent)) Grouping Operation GAÁ AL(R) Groups tuples of R by grouping attributes, GA, and then applies aggregate function list, AL, to define a new relation. AL contains one or more (, ) pairs. Resulting relation contains the grouping attributes, GA, along with results of each of the aggregate functions. Example – Grouping Operation Find the number of staff working in each branch and the sum of their salaries. Example – Grouping Operation Find the number of staff working in each branch and the sum of their salaries. rR(branchNo, myCount, mySum) branchNo Á COUNT staffNo, SUM salary (Staff) Chapter 5 - Objectives Meaning of the term relational completeness. How to form queries in relational algebra. How to form queries in tuple relational calculus. How to form queries in domain relational calculus. Categories of relational DML.  CITS1402 Relational Database Management Systems Week 4—Entity Relationship Modeling Dr Mehwish Nasim/ CSSE Sem1/2024 [email protected]  Contents How to use Entity– Entity Week4 Relationship (ER) modelling Relationship in database design. CITS1402 Attributes Basic concepts associated with Structural Constraints ER model. How to identify and resolve problems with ER models Diagrammatic technique for called connection traps. displaying ER model using Unified Modelling Language (UML). Chapter 12 - Objectives How to use Entity–Relationship (ER) modelling in database design. Basic concepts associated with ER model. Diagrammatic technique for displaying ER model using Unified Modelling Language (UML). Entity Relationship Attributes Structural Constraints How to identify and resolve problems with ER models called connection traps. 3 Steps of a Database Design Conceptual Model Logical Model Physical Model 4 Chapter 12 - Objectives How to use Entity–Relationship (ER) modelling in database design. Basic concepts associated with ER model. Diagrammatic technique for displaying ER model using Unified Modelling Language (UML). Entity Relationship Attributes Structural Constraints How to identify and resolve problems with ER models called connection traps. 5 Concepts of the ER Model Relationship Entity types Attributes types 6 Chapter 12 - Objectives How to use Entity–Relationship (ER) modelling in database design. Basic concepts associated with ER model. Diagrammatic technique for displaying ER model using Unified Modelling Language (UML). Entity Relationship Attributes Structural Constraints How to identify and resolve problems with ER models called connection traps. Alternate Diagrammatic techniques Entity Type Entity type Group of objects with same properties, identified by enterprise as having an independent existence. Entity occurrence Uniquely identifiable object of an entity type. 9 Examples of Entity Types ER diagram of Staff and Branch entity types ER diagram of Staff and Branch entity types Chapter 12 - Objectives How to use Entity–Relationship (ER) modelling in database design. Basic concepts associated with ER model. Diagrammatic technique for displaying ER model using Unified Modelling Language (UML). Entity Relationship Attributes Structural Constraints How to identify and resolve problems with ER models called connection traps. Relationship Types Relationship type Relationship occurrence Set of meaningful associations among Uniquely identifiable association, which entity types. includes one occurrence from each participating entity type. Semantic net of Has relationship type ER diagram of Branch Has Staff relationship Branch has Staff ER diagram of Branch Has Staff relationship Branch has Staff Relationship Types Degree of a Relationship Number of participating entities in relationship. Relationship of degree : two is binary – most relationship are binary three is ternary four is quaternary. 18 Binary relationship called POwns Private owner owns property for rent Binary relationship called POwns Private owner owns property for rent Ternary relationship called Registers Staff register a client at a branch Ternary relationship called Registers Staff register a client at a branch Quaternary relationship called Arranges A solicitor arranges a bid on behalf of a buyer supported by a Financial institution Quaternary relationship called Arranges A solicitor arranges a bid on behalf of a buyer supported by a Financial institution Relationship Types Relationship type where same Recursive Relationship entity type participates more than once in different roles. Relationships may be given role names to indicate purpose that each participating entity type plays in a relationship. Recursive relationship called Supervises with role names Staff (supervisor) supervises staff (supervisee) Recursive relationship called Supervises with role names Staff (supervisor) supervises staff (supervisee) Entities associated through two distinct relationships with role names Manager manages branch office Branch office has a member of staff Entities associated through two distinct relationships with role names Manager manages branch office Branch office has a member of staff Chapter 12 - Objectives How to use Entity–Relationship (ER) modelling in database design. Basic concepts associated with ER model. Diagrammatic technique for displaying ER model using Unified Modelling Language (UML). Entity Relationship Attributes Structural Constraints How to identify and resolve problems with ER models called connection traps. Attributes Attribute Property of an entity or a relationship type. Attribute Domain Set of allowable values for one or more attributes. 31 Attributes Attribute composed of a single Simple Attribute component with an independent existence. Attribute composed of multiple Composite Attribute components, each with an independent existence. 32 Attributes Single-valued Attribute Attribute that holds a single value for each occurrence of an entity type. Multi-valued Attribute Attribute that holds multiple values for each occurrence of an entity type. 33 Attributes Derived Attribute Attribute that represents a value that is derivable from value of a related attribute, or set of attributes, not necessarily in the same entity type. 34 ER diagram of Staff and Branch entities and their attributes Staff: staffNo, name, position, salary, totalStaff Branch: branchNo, address, telephoneNumber ER diagram of Staff and Branch entities and their attributes Staff: staffNo, name, position, salary, totalStaff Branch: branchNo, address, telephoneNumber Keys Candidate Key Minimal set of attributes that uniquely identifies each occurrence of an entity type. Primary Key Candidate key selected to uniquely identify each occurrence of an entity type. Composite Key A candidate key that consists of two or more attributes. Entity Type Strong Entity type that is not existence- Entity Type dependent on some other entity type. Weak Entity Entity type that is existence- Type dependent on some other entity type. 39 Strong entity type called Client and weak entity type called Preference Strong entity type called Client and weak entity type called Preference No primary key Can only be uniquely identified when in a relationship with Client Relationship called Advertises with attributes Newspaper There is a date advertises and cost for property for the advertises rent. property 42 Relationship called Advertises with attributes Newspaper advertises property for rent. There is a date and cost for the advertises property Chapter 12 - Objectives How to use Entity–Relationship (ER) modelling in database design. Basic concepts associated with ER model. Diagrammatic technique for displaying ER model using Unified Modelling Language (UML). Entity Relationship Attributes Structural Constraints How to identify and resolve problems with ER models called connection traps. Structural Constraints Main type of constraint on relationships is called multiplicity. Multiplicity - number (or range) of possible occurrences of an entity type that may relate to a single occurrence of an associated entity type through a particular relationship. Represents policies (called business rules) established by user or company. Structural Constraints The most common degree for relationships is binary. Binary relationships are generally referred to as being: one-to-one (1:1) one-to-many (1:*) many-to-many (*:*) 46 Semantic net of Staff Manages Branch relationship type Multiplicity of Staff Manages Branch (1:1) relationship Each branch is managed by one member of staff A member of staff can manage zero or one branch Multiplicity of Staff Manages Branch (1:1) relationship Each branch is managed by one member of staff A member of staff can manage zero or one branch Semantic net of Staff Oversees PropertyForRent relationship type Multiplicity of Staff Oversees PropertyForRent (1:*) relationship type Each property for rent is overseen by zero or one member of staff Each member of staff oversees zero or more properties for rent 51 Multiplicity of Staff Oversees PropertyForRent (1:*) relationship type Each property for rent is overseen by zero or one member of staff Each member of staff oversees zero or more properties for rent Semantic net of Newspaper Advertises PropertyForRent relationship type Multiplicity of Newspaper Advertises PropertyForRent (*:*) relationship Each property for rent is advertised in zero or more papers Each newspaper advertises one or more properties for rent 54 Multiplicity of Newspaper Advertises PropertyForRent (*:*) relationship Each property for rent is advertised in zero or more papers Each newspaper advertises one or more properties for rent Structural Constraints Multiplicity for Complex Relationships Number (or range) of possible occurrences of an entity type in an n-ary relationship when other (n-1) values are fixed. 56 Semantic net of ternary Registers relationship with values for Staff and Branch entities fixed Multiplicity of ternary Registers relationship Summary of multiplicity constraints Structural Constraints Multiplicity is made up of two types of restrictions on relationships: participation and cardinality Structural Constraints Participation Determines whether all or only some entity occurrences participate in a relationship. Usually: 0 or 1 Cardinality Describes maximum number of possible relationship occurrences for an entity participating in a given relationship type. Usually: 1, n, or * Multiplicity as cardinality and participation constraints Chapter 12 - Objectives How to use Entity–Relationship (ER) modelling in database design. Basic concepts associated with ER model. Diagrammatic technique for displaying ER model using Unified Modelling Language (UML). Entity Relationship Attributes Structural Constraints How to identify and resolve problems with ER models called connection traps. Problems with ER Models Problems may arise when designing a conceptual data model called connection traps. Often due to a misinterpretation of the meaning of certain relationships. Two main types of connection traps are called fan traps and chasm traps. 64 Problems with ER Models Where a model represents a relationship between Fan Trap entity types, but pathway between certain entity occurrences is ambiguous. Where a model suggests the existence of a Chasm Trap relationship between entity types, but pathway does not exist between certain entity occurrences. 65 An Example of a Fan Trap At which branch office does staff number SG37 work? Semantic Net of ER Model with Fan Trap At which branch office does staff number SG37 work? Restructuring ER model to remove Fan Trap Semantic Net of Restructured ER Model with Fan Trap Removed SG37 works at branch B003. An Example of a Chasm Trap At which branch office is property PA14 available? Semantic Net of ER Model with Chasm Trap At which branch office is property PA14 available? ER Model restructured to remove Chasm Trap Semantic Net of Restructured ER Model with Chasm Trap Removed Chapter 12 - Objectives How to use Entity–Relationship (ER) modelling in database design. Basic concepts associated with ER model. Diagrammatic technique for displaying ER model using Unified Modelling Language (UML). Entity Relationship Attributes Structural Constraints How to identify and resolve problems with ER models called connection traps.  CITS1402 Relational Database Management Systems Week 5—Enhanced Entity Relationship Modeling Dr Mehwish Nasim/ CSSE Sem1/2024 [email protected]  Contents Limitations of basic concepts Entity Week5 of the ER model Relationship CITS1402 Specialization/Generalization Attributes Aggregation and Composition Structural Constraints Enhanced Entity-Relationship Model There has been an increase in emergence of new database applications with more demanding requirements. Basic concepts of ER modeling are not sufficient to represent requirements of newer, more complex applications. Response is development of additional ‘semantic’ modeling concepts. The Enhanced Entity-Relationship Model Semantic concepts are incorporated into the original ER model and called the Enhanced Entity-Relationship (EER) model. Examples of additional concept of EER model is called specialization / generalization. Specialization / Generalization Superclass An entity type that includes one or more distinct subgroupings of its occurrences. Subclass A distinct subgrouping of occurrences of an entity type. Superclass {participation,disjoint} SubclassA SubclassB Specialization / Generalization Superclass/subclass relationship is one-to-one (1:1). Superclass may contain Disjoint constraint overlapping or distinct subclasses. AND or OR Not all members of a Participation constraint superclass need be a member of a subclass. Mandatory or Optional 6 Specialization / Generalization Attribute Inheritance An entity in a subclass represents same ‘real world’ object as in superclass May possess subclass-specific attributes, as well as those associated with the superclass. 7 Specialization / Generalization Process of maximizing differences between Specialization members of an entity by identifying their distinguishing characteristics. Process of minimizing differences between Generalization entities by identifying their common characteristics. 8 AllStaff relation holding details of all staff Specialization/generalization of Staff entity into subclasses representing job roles Specialization/generalization of Staff entity into subclasses representing job roles Specialization/generalization of Staff entity into job roles and contracts of employment Specialization/generalization of Staff entity into job roles and contracts of employment EER diagram with shared subclass and subclass with its own subclass EER diagram with shared subclass and subclass with its own subclass Constraints on Specialization/Generalization Two constraints that may apply to a specialization/generalization: participation constraints disjoint constraints Superclass {participation,disjoint} SubclassA SubclassB Constraints on Specialization/Generalization Participation constraint Determines whether every member in superclass must participate as a member of a subclass (mandatory) or not (optional) Disjoint constraint Describes relationship between members of the subclasses and indicates whether member of a superclass can be a member of one (OR), or more than one (AND), subclass. May be disjoint (OR) or nondisjoint (AND). 17 Constraints on Specialization/Generalization There are four categories of constraints of specialization and generalization: - mandatory and disjoint (OR) Must be one, but only one type - optional and disjoint (OR) Does not need to be one, but only one type - mandatory and nondisjoint (AND) Must be one and can be more than one subtype - optional and nondisjoint (AND) Does not need to be one and can be more than one subtype DreamHome example: Staff Superclass with Supervisor and Manager subclasses DreamHome example: Staff Superclass with Supervisor and Manager subclasses DreamHome example: Owner Superclass with PrivateOwner and BusinessOwner subclasses DreamHome example: Owner Superclass with PrivateOwner and BusinessOwner subclasses DreamHome example: Person superclass with Staff, PrivateOwner, and Client subclasses Aggregation and Composition Aggregation represents a “has-a” or “is-part-of” relationship between entity types one represents the “whole” whole one represents the “part” life times are not linked Branch (whole) has Staff (part) part Composition where there is a strong ownership and coincidental lifetime between the whole and part part whole Aggregation and Composition Should be used only when there is a requirement to emphasize special relationship between entity types Implications on creation, update, and deletion Should only use enhanced concepts when the enterprise data is too complex to use only the basic ER model Review Limitations of basic concepts of the ER model Specialization/Generalization Aggregation and Composition  CITS1402 Relational Database Management Systems Week 6—Conceptual Database Design Dr Mehwish Nasim/ CSSE Sem2/2024 [email protected]  Contents How to use Entity– Entity Week6 Relationship (ER) modelling Relationship in database design. CITS1402 Attributes Basic concepts associated with Structural Constraints ER model. Conceptual Database Design Chapter 16 - Objectives The purpose of a design methodology. Database design has three main phases: conceptual, logical, and physical design. How to decompose the scope of the design into specific views of the enterprise. Chapter 16 - Objectives How to use Entity–Relationship (ER) modeling to build a conceptual data model based on the data requirements of an enterprise. How to validate the resultant conceptual model to ensure it is a true and accurate representation of the data requirements enterprise. Chapter 16 - Objectives How to document the process of conceptual database design. End-users play an integral role throughout the process of conceptual database design. Design Methodology A structured approach that uses procedures, techniques, tools, and documentation aids to support and facilitate the process of design. Database Design Methodology Three main phases 1. Conceptual database design 2. Logical database design 3. Physical database design Conceptual Database Design The process of constructing a model of the data used in an enterprise, independent of all physical considerations. Logical Database Design The process of constructing a model of the data used in an enterprise based on a specific data model (e.g. relational), but independent of a particular DBMS and other physical considerations. Physical Database Design The process of producing a description of the implementation of the database on secondary storage Describes the Base relations File organizations Indexes design used to achieve efficient access to the data Any associated integrity constraints and security measures Critical Success Factors in Database Design Work interactively with the users as much as possible. Follow a structured methodology throughout the data modelling process. Employ a data-driven approach. Incorporate structural and integrity considerations into the data models. Combine conceptualization, normalization, and transaction validation techniques into the data modelling methodology. Critical Success Factors in Database Design Use diagrams to represent as much of the data models as possible. Use a Database Design Language (DBDL) to represent additional data semantics. Build a data dictionary to supplement the data model diagrams. Be willing to repeat steps! Overview Database Design Methodology Conceptual database design Step 1 Build conceptual data model Step 1.1 Identify entity types Step 1.2 Identify relationship types Step 1.3 Identify and associate attributes with entity or relationship types Step 1.4 Determine attribute domains Step 1.5 Determine candidate, primary, and alternate key attributes Overview Database Design Methodology Step 1 Build conceptual data model (continue) Step 1.6 Consider use of enhanced modeling concepts (optional step) Step 1.7 Check model for redundancy Step 1.8 Validate conceptual model against user transactions Step 1.9 Review conceptual data model with user Overview Database Design Methodology Logical database design for the relational model Step 2 Build and validate logical data model Step 2.1 Derive relations for logical data model Step 2.2 Validate relations using normalization Step 2.3 Validate relations against user transactions Step 2.4 Define integrity constraints Overview Database Design Methodology Step 2 Build and validate logical data model (continue) Step 2.5 Review logical data model with user Step 2.6 Merge logical data models into global model (optional step) Step 2.7 Check for future growth Overview Database Design Methodology Physical database design for relational database Step 3 Translate logical data model for target DBMS Step 3.1 Design base relations Step 3.2 Design representation of derived data Step 3.3 Design general constraints Overview Database Design Methodology Step 4 Design file organizations and indexes Step 4.1 Analyze transactions Step 4.2 Choose file organization Step 4.3 Choose indexes Step 4.4 Estimate disk space requirements Overview Database Design Methodology Step 5 Design user views Step 6 Design security mechanisms Step 7 Consider the introduction of controlled redundancy Step 8 Monitor and tune the operational system Step 1 Build Conceptual Data Model To build a conceptual data model of the data requirements of the enterprise. Model comprises entity types, relationship types, attributes and attribute domains, primary and alternate keys, and integrity constraints. Step 1.1 Identify entity types Look for nouns and noun phrases Watch out for synonyms and homonyms Step 1.2 Identify relationship types important relationships that exist between the entity Looks for verbs and verbal expressions Check for fan and chasm traps Extract from data dictionary for Staff user views of DreamHome showing description of entities Step 1 Build Conceptual Data Model To build a conceptual data model of the data requirements of the enterprise. Model comprises entity types, relationship types, attributes and attribute domains, primary and alternate keys, and integrity constraints. Step 1.1 Identify entity types Look for nouns and noun phrases Watch out for synonyms and homonyms Step 1.2 Identify relationship types important relationships that exist between the entity Looks for verbs and verbal expressions Check for fan and chasm traps Extract from data dictionary for Staff user views of DreamHome showing description of relationships Step 1 Build Conceptual Data Model Step 1.3 Identify and associate attributes with entity or relationship types To associate attributes with the appropriate entity or relationship types and document the details of each attribute. “What information are we required to hold on X” Create a list of attributes and associate with entities Watch out for similar attributes across entities Same entity (EER), Foreign Keys => relationship Step 1.4 Determine attribute domains To determine domains for the attributes in the data model and document the details of each domain. Extract from data dictionary for Staff user views of DreamHome showing description of attributes First-cut ER diagram for Staff user views of DreamHome Material till this slide is part of the mid-term Step 1 Build Conceptual Data Model Step 1.5 Determine candidate, primary, and alternate key attributes To identify the candidate key(s) for each entity If there is more than one candidate key, to choose one to be the primary key that has Minimal set of attributes Least likely to have its values changed Fewest characters Smallest minimum value Easiest to use from the users perspective ER diagram for Staff user views of DreamHome with primary keys added Step 1 Build Conceptual Data Model Step 1.6 Consider use of enhanced modeling concepts (optional step) To consider the use of enhanced modeling concepts, such as specialization / generalization, aggregation, and composition. Revised ER diagram for Staff user views of DreamHome with specialization or generalization Step 1 Build Conceptual Data Model Step 1.7 Check model for redundancy To check for the presence of any redundancy in the model and to remove any that does exist. Re-examine 1:1 relationships Remove redundant relationships Consider time dimension Example of removing a redundant relationship called Rents Example of a non-redundant relationship FatherOf Step 1 Build Conceptual Data Model Step 1.8 Validate conceptual model against user transactions To ensure that the conceptual model supports the required transactions. Step1.9 Review conceptual data model with user To review the conceptual data model with the user to ensure that the model is a ‘true’ representation of the data requirements of the enterprise. Step 1 Build Conceptual Data Model Transaction (d): List the details of properties managed by a named member of staff at a branch Describe transaction PropertyForRent contains property details Staff contains details of staff members Relationship Staff manages PropertyForRent Identify transaction on ER model Using pathways to check that the conceptual model supports the user transactions Overview Database Design Methodology Conceptual database design Step 1 Build conceptual data model Step 1.1 Identify entity types Step 1.2 Identify relationship types Step 1.3 Identify and associate attributes with entity or relationship types Step 1.4 Determine attribute domains Step 1.5 Determine candidate, primary, and alternate key attributes Overview Database Design Methodology Step 1 Build conceptual data model (continue) Step 1.6 Consider use of enhanced modeling concepts (optional step) Step 1.7 Check model for redundancy Step 1.8 Validate conceptual model against user transactions Step 1.9 Review conceptual data model with user