New User Special Price Expires in

Let's log you in.

Sign in with Facebook


Don't have a StudySoup account? Create one here!


Create a StudySoup account

Be part of our community, it's free to join!

Sign up with Facebook


Create your account
By creating an account you agree to StudySoup's terms and conditions and privacy policy

Already have a StudySoup account? Login here

Review 1

by: loveena Cherukunnathu
loveena Cherukunnathu

Preview These Notes for FREE

Get a free preview of these Notes, just enter your email below.

Unlock Preview
Unlock Preview

Preview these materials now for free

Why put in your email? Get access to more of this material and other relevant free materials for your school

View Preview

About this Document

Database Management Systems
Karen Scott
Study Guide
uta database
50 ?




Popular in Database Management Systems

Popular in Information technology

This 7 page Study Guide was uploaded by loveena Cherukunnathu on Saturday March 12, 2016. The Study Guide belongs to INSY 3304 - 002 at University of Texas at Arlington taught by Karen Scott in Spring 2016. Since its upload, it has received 198 views. For similar materials see Database Management Systems in Information technology at University of Texas at Arlington.

Similar to INSY 3304 - 002 at UTA

Popular in Information technology


Reviews for Review 1


Report this Material


What is Karma?


Karma is the currency of StudySoup.

You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!

Date Created: 03/12/16
INSY 3304 Midterm Exam Review  Components of a DB System  Hardware  physical things (ex: computer)  Software  It contains: operating system software, DBMS, application programs that access with DBMS(plus utility software for backups)  People  database designer (who does the design modeling), system and database admins, systems analysist and programmers, and users  Procedures  Business rules, the instructions/rules/polices that dictate the design and use of the system.  Data  collection of facts that are stored in your database  Database systems are created and managed at different levels of complexity  Some maybe small/ some maybe big (ex. for 1 person or whole industry)  Database solutions must be cost-effective, tactic and strategic  Database technology already in use affects selection of a database system  Functions of a DBMS  Transparency  It should be transparent to the end user which means, it hides the complexity from the user.  Data dictionary management  Stores the definitions of data elements and their relationships.  DBMS looks up data components structures and relationships. It automatically records any structural changes in its data dictionary. Also it provides data abstractions and removes structural and data dependency  Data storage management  Create and manage the data structure that are required for data storage. (ex. create tables, columns)  Also create data entry forms, reports, screen definitions, query definitions…etc.  Performs fine tuning for you so database perform more efficiently. (ex. create index)  Data transformation and presentation  Transforms from physically to logical and vis-versa. Transforms data to conform to the required data structures.  Security management  DBMS creates security system that enforces user’s data security and privacy.  Multiuser access control  multiple concurrent users  Takes complex algorithms to maintain the integrity of the data while allowing for concurrent access.  Backup and recovery management  Backups your data - makes copy of your data and restore if it something is lost.  Backup and data recover to ensure data safety and integrity. Critical to preserve the integrity of the data  Data integrity management  DBMS force and enforce integrity rules. Minimizes redundancy, max consistency.  The integrity rules are stored in data dictionary.  Referential integrity: Enforces data integrity based on the relationships  Entity integrity: Primary key’s has to be different from once that already exist.  Database access languages and APIs  SQL is default query language nowadays. SQL is supported by majority of DBMS vendors. Query languages are non-procedural.  API (Application Program Interface): Programs that allow 1 application to communicate with another application.  Database communication interfaces  DBMS accepts end-user requests. You can go directly in DBMS and create rd different quarries and reports. Also allows you to connect to 3 party application  Different types of database systems (by location and number of users)  You have different vendors for database and you also have different types of databases.  Databases classifications o Number of users o Database location(s) o Expected type and extent of use  Number of users  Single-user database: only one user at a time. (A.K.A. desktop database)  Multi-user database: multiple users using the database at the same time.  Work group database: a department or one small organization (less than 50 people)  Enterprise database: multiple department, large companies (over 50 people)  Location  This is where the data is actually stored.  Physical location is where data is stored (ex: in disk or the actual location)  Logical location is how the user sees it.  You can have a centralized location: all data at a single cite physically at one spot  Decentralized location: data is distributed physically at different areas but logically viewed as single location.  Type and Extent of Use  Extent (A.K.A. transactional or productional database): operational database which support the companies day to day operations (ex: payroll, inventory).  Data warehouse and decision support: for tactical or strategic decision making (ex: see what this company produced in long term and see if they should continue or not). All the long term data is stored here.  We can store different data in these databases.  Unstructured  Unstructured is data that is in its original state.  Structured  Structure is data resulting from formatting. (we usually don’t store data like this)  Semi-structured  Semi-structured is in between both unstructured and structured. Data processed to some extent.  Ex: some calculated that have total (but we can calculate the total anytime so why store data in this form?) or if we add “$” to money data (but it is not necessary to add it and store that data)  XML  XML is Extensible Markup Language.  It represent data elements in textual format.  XML format supports semi-structured XML data.(ex: you can use customized tag like <title><text>)  Basic concepts about the relational database model – Entity: Primary key’s has to be different from once that already exist – Attribute: (Property/field). Refers to the initial filing system. Specific characteristic of it. – Table: - A.K.A relations. - Relational model is based on collection of table/relations - Each table represents an entity. - Each table is matrix of rows and columns. - This model greatly reduced the amount of duplicate data. – Row: Each row in a table is referred as a tuple – Column: Each rows and columns intersect and data can be stored in those. – Attribute domain: set of values allowed in an attribute  Types of keys – Primary: 1 or more attributes that uniquely identifies each row in a table - Natural: key that is formed of attributes that already exist in the real world - Surrogate: candidate key - Entity Integrity: each row can be uniquely identified. Each row much have a unique identifier. provided by primary keys  Foreign: 1 or more attributes that value matches the primary key in another table. 1 or more attributes that reference the entire primary key of another table - Referential Integrity: if a foreign key contains a value, it must refer to an existing a valid row in a table/relation. Provided by foreign keys  Super: A key that unique identifies each row. A category of a key. All primary key, but not all superkey is a primary key. – Candidate: a super key without unnecessary attributes – Secondary: used for data retrieval purposes. Don’t have to be unique  Union compatibility: types of columns must match  Relational database operators (definitions and union compatibility requirements) – Union: add the rows together into tables. They must be union compactable (they must have the same types of columns) – Intersection: must be union compactable (still same attribute types even if its named differently). Intersect to see what is in both tables. Yield a result in what exists in both tables – Difference: rows of table A – rows of table B. result will yield, whats in 1 tablet but not in 2nd table. Have to be union compactable  Product: multiples tables together to get a Cartesian product. Doesn’t have to be union compactable  Selection: A select retrieves horizontal subset of reference tables. Gives u all the columns but narrow down the rows – Projection: yield vertical substance of the table – Joins : - Natural join: link the tables together by selecting rows by common values in their common attributes - Equijoin: link the tables based on condition of equality, comparing specific tables - Theta join: linking tables bases on any comparison operator other than = sign - Outer join: links tables and retains the matched pairs and any un matched values from 2 table are left null – Divide: 2 column table by 1 column table, result will yield any value in the unmatched column that appears all the values in the 1 column table  Data dictionary  Provides detailed accounting of all tables found within the user/designer- created database  Contains (at least) all the attribute names and characteristics for each table in the system  Contains metadata: data about data  System catalog  Contains metadata  Detailed system data dictionary that describes all objects within the database  Data models – Conceptual: identifies the highest-level relationships between the different entities. No attributes or primary keys identified. – Internal: representation of the database as seen by the DBMS – External: Represent the user’s view of the database – Physical: lowest level of abstractions. This is how the data is stored.  E-R modeling concepts – Entities - Weak: exist when PK of related entity doesn’t contain PK of parent entity 1. it has to be existence dependency 2. PK that is partially or totally derived from PK of parent entity - Composite:  Also known as bridge entities  Used to implement M:N relationships  Composed of primary keys of each of the entities to be connected  May also contain additional attributes that play no role in connective process – Attributes - Simple: can’t be sub divided. Its an atomic in nature st - Composite: it can be sub divided(ex: persons name – 1 name, middle name, last name) - single valued: can only have 1 value, even over time it wont change (ex: DOB) – Relationships - Cardinality: Expresses minimum and maximum number of entity occurrences associated with one occurrence of related entity (ex: credit hour class: 1,21) - Connectivity: Describes the relationship classification - Participation:  Optional: one entity occurrence doesn’t require corresponding entity occurrence( dependency)  Mandatory: one entity occurrence requires a corresponding entity occurrence - Existence dependency: A semantic control that indicates that an instance of one entity cannot exist unless an instance of a related entity also exists  Normalization: the process for evaluating and correcting table structures to minimize data redundancy  Dependencies – Functional dependency: a constraint that describes the relationship between attributes in a relation – Full functional dependency: full functional dependency occurs when you already meet the requirements for a functional dependency and the set of attributes on the left side of the functional dependency statement cannot be reduced any farther – Partial dependency: none key attribute can be determined by part of primary key (only applies to composite key) – Transitive dependency: exist when a non-key attribute can be determined by another non key attribute  Forms of normalization – 1NF - Step 1: Eliminate the Repeating Groups - Eliminate nulls: each repeating group attribute contains an appropriate data value - Step 2: Identify the Primary Key - Must uniquely identify attribute value - New key must be composed - Step 3: Identify All Dependencies - Dependencies are depicted with a diagram – 2NF - Step 1: Make New Tables to Eliminate Partial Dependencies - Write each key component on separate line, then write original (composite) key on last line - Each component will become key in new table - Step 2: Reassign Corresponding Dependent Attributes - Determine attributes that are dependent on other attributes - At this point, most anomalies have been eliminated - – 3NF - Step 1: Make New Tables to Eliminate Transitive Dependencies - For every transitive dependency, write its determinant as PK for new table - Determinant: any attribute whose value determines other values within a row - Step 2: Reassign Corresponding Dependent Attributes - Identify attributes dependent on each determinant identified in Step 1 - Identify dependency - Name table to reflect its contents and function  Denormalization – what and why?  Creation of normalized relations is important database design goal  Processing requirements should also be a goal  If tables are decomposed to conform to normalization requirements:  Number of database tables expands  SQL: Structural Query Lang. default database lang. its non-procedural lang. – DDL: Data Definition Lang (you can definite the structures) – DML: data manipulation lang. (add, modify, delete, retrieve data)  SQL Statements – CREATE DATABASE: Create and reserve where the database will be stored – Physical files: that will create a physical files in a disk – CREATE TABLE: contain the data inside the database – ALTER TABLE (add columns, delete columns, add constraints) – DROP TABLE: drop table previously stored in the system – INSERT: Inserts multiple rows from another table – UPDATE: Modify data in a table – DELETE: Deletes a table row – SELECT: Used to list contents of table – COMMIT: saving to disk. use after inserts, updates or delete – ROLLBACK: undo  Other – DISTINCT: used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only unique records. – ORDER BY: used to sort the data in ascending or descending order Note: Exam will be closed book, closed notes. Questions will be True/False, Multiple Choice, and Short Answer.


Buy Material

Are you sure you want to buy this material for

50 Karma

Buy Material

BOOM! Enjoy Your Free Notes!

We've added these Notes to your profile, click here to view them now.


You're already Subscribed!

Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'

Why people love StudySoup

Steve Martinelli UC Los Angeles

"There's no way I would have passed my Organic Chemistry class this semester without the notes and study guides I got from StudySoup."

Jennifer McGill UCSF Med School

"Selling my MCAT study guides and notes has been a great source of side revenue while I'm in school. Some months I'm making over $500! Plus, it makes me happy knowing that I'm helping future med students with their MCAT."

Jim McGreen Ohio University

"Knowing I can count on the Elite Notetaker in my class allows me to focus on what the professor is saying instead of just scribbling notes the whole time and falling behind."

Parker Thompson 500 Startups

"It's a great way for students to improve their educational experience and it seemed like a product that everybody wants, so all the people participating are winning."

Become an Elite Notetaker and start selling your notes online!

Refund Policy


All subscriptions to StudySoup are paid in full at the time of subscribing. To change your credit card information or to cancel your subscription, go to "Edit Settings". All credit card information will be available there. If you should decide to cancel your subscription, it will continue to be valid until the next payment period, as all payments for the current period were made in advance. For special circumstances, please email


StudySoup has more than 1 million course-specific study resources to help students study smarter. If you’re having trouble finding what you’re looking for, our customer support team can help you find what you need! Feel free to contact them here:

Recurring Subscriptions: If you have canceled your recurring subscription on the day of renewal and have not downloaded any documents, you may request a refund by submitting an email to

Satisfaction Guarantee: If you’re not satisfied with your subscription, you can contact us for further help. Contact must be made within 3 business days of your subscription purchase and your refund request will be subject for review.

Please Note: Refunds can never be provided more than 30 days after the initial purchase date regardless of your activity on the site.