DATABASE PROCESS MGT
DATABASE PROCESS MGT ISDS 3110
Popular in Course
Popular in Info Systems & Decision Sciences
This 7 page Class Notes was uploaded by Winston Dickens II on Tuesday October 13, 2015. The Class Notes belongs to ISDS 3110 at Louisiana State University taught by L. Hutchinson in Fall. Since its upload, it has received 27 views. For similar materials see /class/222497/isds-3110-louisiana-state-university in Info Systems & Decision Sciences at Louisiana State University.
Reviews for DATABASE PROCESS MGT
Report this Material
What is Karma?
Karma is the currency of StudySoup.
Date Created: 10/13/15
CHAPTER 1 Major Database Concepts Data raw facts Information the result of processing raw data to reveal meaning Database shared integrated computer structure that houses End user data raw facts of interest to the end user Metadata data about data Database Management System DBS collection of programs that manages the database structure and controls access to the data stored in the database DBMS o O O O O O O O O O 0 Stores data structure Relationships Defines all required access paths Data base systems Type depends on of users Scope Location and use File system Vs Database I File System I Datab Functions Each file owned by individual or department that created it Each file used its own application programs Every task requires programming Time consuming File structure changes req modifications Data redundancy Uncontrolled sets stage for I Data inconsistency I Data anomalies Modification insertion and deletion ase Systems Complexity depends on size function structure activities Solutions must be cost and strategically effective Data Dictionary Management where DB stores definitions of the data elements and their relationship smetadata Data Storage Management create and manage complex structures required for data storage Data Transformation and Management transforms entered data to required data structures Security Ma nagement creates security system that enforces user security and data privacy Multiuser access control DBMS systems use sophisticated algorithms that allow multiuser access Backup and integrity recovery management provides backup and recovery to ensure data safety and Data integrity management promotes and enforces integrity rules Minimizing redundancy CHAPTER 2 Basic Building blocks 0 Entity person place or thingevent about which data are to be collected and stored O Attribute a characteristic of an entity 0 Relationship describes an association among entities and are BDRECTONAL I One to many 1M Relationship I Many to manyMN relationship One to one 11 relationship 0 Constraint a restriction placed on the data Business Rules 0 Description of policies procedures principles within organization 0 Generally I Nouns translate to entities I Verbs translate into relationships among entities 0 Relationships are bidirectional 0 Questions I How many instances of B are related to one instance of A How many instances of A are related to one instance of B Database Models 0 Collection of logical constructs used to represent the data structure and the data relationships found within the database I Conceptual what is represented global view ofentire DB I Entity Relationship Model I Widely accepted and adapted graphical tool for data modeling I Graphical representation of entities and their relationships in a database structure I Implementation how the data are represented I Relational model I SQL based relational database includes User interface I Set of tables I SQL 39engine39 I Most important advantage of relational database is to hide complexities of the relational model from the user I RDBMS manages the physical details while the user just sees tables I Advantages I Data and structural independence I Easy to design manage I Less programming effort Structured Query LanguageSQL I Disadvantages I Requires substantial hardware and OS overhead Tends to be slower than other DBMS I Network model 39 Allowed a record to have more than one parent I Set a relationship is called a set each set is composed of at least two record types an ownerparent and a member child record 39 Hierarchical model I Basic Structure I Collection of records perceived as organized to conform to upside down tree structure I Tree structure is represented as a hierarchical path on the computer39s storage media Contains levelssegments of parents and children layers I OnetoMany 1M I Each parent can have many children each child has only one parent Models 0 Conceptual global view of entire DB I ER model 0 External end user39s view of the data I Subset of DB share a common entity 0 Internal map conceptual model to DBMS Actual SQL used to create tables PK FK data types etc CHAPTER 3 Data model building blocks I Entity person place or thingevent about which data are to be collected and stored I Attribute characteristic of an entity field I Relationship association among entities 1M 11 MN entity set DBMS data Types numeric character date logical Null Values I Occurs when a cell is blank not same as space I Cannot be a PK Can create problems Keys I Primary Key PK a chosen candidate key which uniquely identifies a row 0 Should not change frequently 0 Based on concept known as determinationfunctional dependence I Composite Key multi attribute primary key Secondary Key an attribute or combo used strictly for data retrieval I Foreign key an attribute or combo that is a primary key in another relation I SuperKey attribute that uniquely identifies each tuple Candidate key a minimal superkey Linking Tables I Linked using Foreign Keys Primary key of one table appears as a quotlinkquot in another table I Controlled redundancy makes a relational database work I Labeled as Productvendcode gtVendorvendcode Integrity Rules I Domain Integrity attribute values must come from appropriate domain I Entity Integrity Primary key values must be unique and cannot be null Referential integrity Foreign key value is either null or has an entry which corresponds to a primary key value in referenced table I Assertion other rules within business environment Operators I Project subset of columns vertical subset I Select subset of rows horizontal subset Join combines tables using links I Union combines tables I Difference subtracts tables I Intersect Yields only rows that appear in both tables Cartesian product Yields all possible pairs of rows from two tables I Divide uses a two column table as a dividend and a one column table as the divisor Relationships 0 Ideal in a RDBMS and it s the main building block 0 Often mean entity components are not defined properly 0 Could mean two entities belong in the same table 0 Sometimes done to make sure info doesn t get lumped into one table 0 Can avoid problems by creating bridge entity I Entities primary key is composed of two primary keys of liked entities I Can contain more attributes than just PK Use diamond with a rectangle to indicate composite entity Data Dictionary Provides detailed accounting of all tables found within the userdesignercreated database I Contains at least all the attribute names and characteristics for each table in the system I Contains metadata System Catalog Contains metadata I Detailed system data dictionary that describes all objects within the DB Indexes Useful for data retrieval I All relations should have an index on the primary key primary key index I Additional indexes often required 0 Secondary indexes are created on appropriate secondary keys I Can be created and dropped easily but can be time consuming C HAPTE R 4 Attributes Characteristics of entities I Required attribute must have a value I All attributes must have a DOMAIN set of possible values I ER model Classes of Attributes 0 Simple attribute that cannot be subdividedthis is what you want Composite attribute that can be further subdivided and yield additional attributes Derived Singlevalued can only have a single value I ie one SS one serial number 0 Multivalued can have many values ie 1 person has several different degrees 1 home has several different phones I RDBMS cannot implement this I To fix I Create new attributes I Create new entity composed of original multivalued attributes components 000 Cardinality I Expresses minimum and maximum number of entity occurrences associated with one occurrence of related entity I Based on business rules I Not typically shown in crows foot Relationship Participation I Optional one entity occurrence does not require a corresponding entity occurrence in a particular relationship 0 Represented by a small circle on side of optional entity 0 Happens when minimums are zero I Mandatory one entity occurrence REQUIRES a corresponding occurrence in a particular relationship 0 Represented by a small hash mark 0 Happens when minimums are one Existence Dependent Entity can only exist if associated with another entity occurrence Happens when you have a foreign key attribute that cannot be null I It can affect the order in which tables are created and implemented 0 Not indicated in drawing of ERD Weak Entities I One that is existencedependentm has a primary key that is a partially derived or totally derived from the parent identity in the relationship n crows foot the line connecting the entities will be a solid line Types of Associations I Unary exists when an association is maintained within a single entity employee is the manager of other employees Binary exists when two entities are associated most common I Ternary exists when three entities are associated happens with the third table created in the MN 0 Composite entitiesbridge entities composed of primary keys of two parent entities and other attributes General Rules for developing ERD I All primary keys must be defined as NOT NULL I Define all foreign keys to conform to the 1 39 for the N r 39 binary 39 39 39 39I O MN Convert the MN to a composite entity consisting of Parent table PK39s O 1M I Create the foreign key by putting the ii r 1quot I 39one39 parent table I 39many39 dependent table I Foreign key always goes on the many side Mandatory I Foreign key usually placed in the stronger entity or the entity most frequently accessed I It is possible that two entities belong in the same entity Optional I When cardinalities are both 01 on both sides very rare A new entity is created I one side optionalForeign key usually placed in the optional entity to avoid null values But could go either way 0 Weak entity I Put the key of the parent table into the weak entity I Weak entity relationship conforms to the same rules as the 1M relationship except fk restrictions Line is solid SQL Structured Query LanguageSQL I Standard language used to communicate with a relational database I Fits into two categories 0 Data definition commands to create table and define access rights 0 Data manipulation commands to insert update delete and retrieve data Know how to do a Create Statement I Creates tables Know how to do an Alter Statement I Adds columns and can modify or drop them as well Know how to do Insert statement Inserts new record into a table 0 If you don39t know the order of the columns or you are missing values you must include a column list in your insert statement insert into Projectclient Projno Know how to do update statement Know how to do a delete statement KNOW HOW TO DO A SELECT statement 3 parts 0 Select includes all the field names you wish to view in the result of your query O From includes all the table names from which you are retrieving information 0 Where includes any stipulations to the data you are retrieving AS WELL AS the table links Join Know how to sort output I Default way is ASCENDING order 0 39Order by 339 means you order by the third field name in the Select line Clarifying Attribute name I When you list an attribute name in SQL statement you can always precede the attribute name with the table name 0 Select projectprojno employeefname 0 Where projectprojnum 39asdfa39 I You are only Required to do so when the attribute which you have listed can be found in more than one of the tables you have in your SQL statement Distinct Clause I Used to suppress the display of duplicate records I Placed in front of the column name Ex Select distinct client From project Dates Date I DayColumnName I MonthColumnName YearColumnName I DatePartntervalColumnName 0 Returns the specified part of the date 0 Example DatePart39yyyy39 date would return the current year of the current date 0 Use 39d39 for day 39m39 for month and 39yyyy39 for year I DateAdd returns the result of adding or subtracting a specified period of time to a date 0 DateAdd39d39 3 ColumnName would add three days to the date in the column indicated DateDiff returns the interval of the time between two dates 0 DateDiff 39d39 ColumnName 12312003 would return days until 12312003