- STRUCTURED: numbers, Text, Dates - UNSTRUCTURED: images, video, documents

SOLUTIONS FOR DATA BASE APPROACH (requires DBMS)

- Central Repository of shared data - Data is managed by a controlling agent - Stored in a standardized convenient form

software system that is used to create, maintain, and provide controlled access to user databases

DBMS or Database Management System

1. New, specialized personnel 2. Installation and management cost and complexity 3. Conversion costs 4. Need for explicit backup and recovery 5. Organizational conflict

COSTS and RISKS of DATABASE APPROACH

software for managing the database

DBMS (Database Management System)

Help

Options

focusNode

Didn't know it?
click below

Knew it?
click below

Don't Know

Remaining cards (0)

Know

retry

shuffle

restart

0:00

Embed Code - If you would like this activity on your web page, copy the script below and paste it into your web page.

Normal Size Small Size show me how

INFO MANAGEMENT

REVIEWER MIDTERMS

Question	Answer
organized collection of logically related data	DATABASE
stored representations of meaningful objects and events	DATA
types of data	- STRUCTURED: numbers, Text, Dates - UNSTRUCTURED: images, video, documents
data processed to increase knowledge in the person using the data..	INFORMATION
data that describes the properties and context of user data	METADATA
process of creating, storing, and accessing content of files	FILE PROCESSING
All programs maintain metadata for each file they use	PROGRAM-DATA DEPENDENCE
Program-Data Dependence ADVANTAGE OR DISADVATANGE OF FILE PROCESSING	DISADVANTAGE
Different systems/ programs have separate copies of the same data	DUPLICATION OF DATA
Duplication of Data ADVANTAGE OR DISADVATANGE OF FILE PROCESSING	DISADVANTAGE
No centralized control of data	LIMITED DATA SHARING
Limited data sharing ADVANTAGE OR DISADVATANGE OF FILE PROCESSING	DISADVANTAGE
Programmers must design their own file formats	LENGTHY DEVELOPMENT TIMES
Lengthy development times ADVANTAGE OR DISADVATANGE OF FILE PROCESSING	DISADVANTAGE
80% of information systems budget	EXCESSIVE PROGRAM MAINTENANCE
Excessive program maintenance ADVANTAGE OR DISADVATANGE OF FILE PROCESSING	DISADVANTAGE
SOLUTIONS FOR DATA BASE APPROACH (requires DBMS)	- Central Repository of shared data - Data is managed by a controlling agent - Stored in a standardized convenient form
meaning of DBMS	Database Management System
software system that is used to create, maintain, and provide controlled access to user databases	DBMS or Database Management System
ADVANTAGE OR DISADVANTAGE of DATABASE APPROACH Program-data independence	ADVANTAGE
ADVANTAGE OR DISADVANTAGE of DATABASE APPROACH Planned data redundancy	ADVANTAGE
ADVANTAGE OR DISADVANTAGE of DATABASE APPROACH Improved data consistency	ADVANTAGE
ADVANTAGE OR DISADVANTAGE of DATABASE APPROACH Improved data sharing	ADVANTAGE
ADVANTAGE OR DISADVANTAGE of DATABASE APPROACH Increased application development productivity	ADVANTAGE
ADVANTAGE OR DISADVANTAGE of DATABASE APPROACH Enforcement of standards	ADVANTAGE
ADVANTAGE OR DISADVANTAGE of DATABASE APPROACH Improved data quality	ADVANTAGE
ADVANTAGE OR DISADVANTAGE of DATABASE APPROACH Improved data accessibility and responsiveness	ADVANTAGE
ADVANTAGE OR DISADVANTAGE of DATABASE APPROACH Reduced program maintenance	ADVANTAGE
ADVANTAGE OR DISADVANTAGE of DATABASE APPROACH Improved decision support	ADVANTAGE
1. New, specialized personnel 2. Installation and management cost and complexity 3. Conversion costs 4. Need for explicit backup and recovery 5. Organizational conflict	COSTS and RISKS of DATABASE APPROACH
DATA MODELS ENTITIES RELATIONSHIPS RELATIONAL DATABASES	ELEMENTS OF DATABASE APPROACH
Graphical system capturing nature of relationship of data	Data Models
high-level entities and relationships for the organization	Enterprise Data Model
more detailed view, matching data structure in database or data warehouse	Project Data model
Noun form describing a person, place, object, event, or concept	Entities
Composed of attributes	Entities
Between entities	Relationships
Usually one-to-many (1:M) or many-to-many (M:N)	Relationships
Database technology involving tables representing entities and primary/foreign keys representing relationships	Relational Databases
computer-aided software engineering	CASE tools
centralized storehouse of metadata	Repository
software for managing the database	DBMS (Database Management System)
storehouse of the data	Database
software using the data	Application Programs
text and graphical displays to users	User Interface
personnel responsible for designing databases and software	System Developers
people who use the applications and databases	End Users
meaning of SDLC	System Development Life cycle
Detailed, well planned development process	SDLC
Time consuming, but comprehensive	SDLC
Long development cycle	SDLC
meaning of RAD	Rapid application Development
cursory attempt at conceptual data modeling	PROTOTYPING
defines database during development of initial prototype	PROTOTYPING
repeat implementation and maintenance activities with new versions	PROTOTYPING
preliminary understanding deliverable request for study	Planning (SDLC)
Database activity-enterprise modeling and early conceptual data modeling	Planning (SDLC)
requirements analysis and structuring	Analysis(SDLC)
functional system specifications	Analysis(SDLC)
Database activity–thorough and integrated conceptual data modeling	ANALYSIS (SDLC)
detailed design specification	LOGICAL DESIGN
develop technology and organizational specifications	PHYSICAL DESIGN (SDLC)
its purpose is programming, testing, training, installation, documenting	Implementation (SDLC)
database activity that includes coded programs, documentation, installation and conversion	Implementation (SDLC)
its purpose is to monitory, repair and enhance	MAINTENANCE (SDLC)
Database activity that does performance analysis and tuning, error corrections	Maintenance (SDLC)
description of the organization and structure of a database	DATABASE SCHEMA
underlying design and implementation	Internal Schema
different people have different views of the database	External Schema
schema for ER models	Conceptual Schema
this includes logical structures and physical structures	Internal Schema
planned undertaking of related activities to reach an objective that has a beginning and an end	Project
initiated and planned in planning stage of SDLC	Project
executed during analysis, design, and implementation and closed at the end of implementation	Proect
•Business analysts • Systems analysts • Database analysts and data modelers • Data/Database administrators • Project managers • Users • Programmers • Database architects • Other technical experts	People who are involved in managing projects
1. Need for data-independence to reduce maintenance 2. desire to manage more complex data types and structures 3. Ease of data access for less technical personnel 4. Need for more powerful decision support platforms	main objectives of Database systems (4)
instance of a person, place, object ,event, concept (often corresponds to a row in a table)	Entities
collection of entities	Entity Type
link between entities	Relationships
category of relationship link between entity types	Relationships
properties or characteristics of an entity or relationship type	Attributes
Entities are represented by softboxes	True
Entity names go in the soft boxes	True
Entity names are always singular and written in capital letters	True
Attributes are listed under entity names	True
Mandatory attributes are marked with an asterisk: “*”	True
Optional attributes are marked with a circle: “o”	True
Unique identifiers are marked with a hash sign: “#”	True
•Declarative–what, not how •Precise–clear, agreed-upon meaning •Atomic–one statement •Consistent–internally and externally •Expressible–structured, natural language •Distinct–non-redundant •Business-oriented–understood by business people	A good business rule
a person, a place, an object, an event, or a concept in the user environment about which the organization wishes to maintain data	Entity
a collection of entities that share common properties or characteristics	Entity Type
A single occurrence of an entity type	Entity Instance
exists independently of other types of entities	Strong Entity
has its own unique identifier (underlined with a single line)	Strong Entity
dependent and cannot exist on its own	Weak entity
does not have a unique identifier	Weak entity
partial identifier only	weak entity
entity box and partial identifier have double lines	Weak entity
Links strong entities to weak entities	relationship
property or characteristic of an entity or relationship type	Attribute
Name should be a singular noun or noun phrase	Naming Attributes
Name should be unique	Naming Attributes
Name should follow a standard format [Entity Type Name{[Qualifier]}]Class	Naming Attributes
Similar attributes of different entity types should use the same qualifiers and classes	Naming Attributes
State what the attribute is and possibly why it is important	Defining attributes
Make it clear what is and is not included in the attribute’s value	Defining attributes
Include aliases in documentation	Defining attributes
State source of values	Defining attributes
Specify required vs. optional	Defining attributes
State min and max number of occurrences allowed	Defining attributes
Indicate relationships with other attributes	Defining attributes
must have a value for every entity or relationship instance with its associated	Mandatory/Required
may not have a value for every entity or relationship instance with its associated	Optional
an attribute that has more attributes or meaningful component parts (attributes)	Composite Attribute
an attribute that may take on more or one value for a given entity or relationship instance	Multivalued Attribute
values can be calculated from related attribute values	Derived Attribute
an attribute that uniquely identifies individual instances of an entity type	Identifier
an attribute that could be a key satisfies the requirements for being an identifier	Candidate Identifier
identifiers cannot change in VALUE and will not be NULL	TRUE
Avoid intelligent identifiers (e.g., containing locations or people that might change)	TRUE
Substitute new, simple keys for long, composite keys	TRUE
is modeled as lines between entity types	Relationship type
between specific entity or relationship instances	Instance
These describe features pertaining to the association between the entities in the relationship	Relationship attributes
entities that have more than one type of relationship between them	Multiple Relationship
combination of relationship and entity	Associative Entity
entity related to another of the same entity type	Unary relationship
entity of two types related to each other	Binary Relationship
entity of three different types related to each other	ternary relationship
entity in relationship will have one related entity	one-to-one
entity on one side of the relationship can have many related entities, but an entity on the other side will have a maximum of one related entity	One-to-many
entities on both sides of the relationship can have many related entities to the other side	many-to-many
has attributes	entity
links entities together	relationship
All relationships for the associative entity should be many	True
The associative entity could have meaning independent of the other entities	True
The associative entity preferably has a unique identifier, and should also have other attributes	True
The associative entity may participate in other relationships other than the entities of the associated relationship	True
Ternary relationships should be converted to associative entities	True
is like a relationship with an attribute, but it is also considered to be an entity in its own right.	Associative entity
extends er model with new modeling constructs	Enhanced ER model
subgrouping of the entities in an entity type that has attributes distinct from those in other subgroupings	Subtype
generic entity type that has a relationship with one or more subtypes	Supertype
inherit values of all attributes of the supertype	Subtype
An instance of a substype is also an instance of the supertype	TRUE
indicate that all subtypes will participate in the relationship	supertype
The instances of a subtype may participate in a relationship unique to that subtype. In this situation, the relationship is shown at the subtype leve	True
The instances of a ______ may participate in a relationship unique to that subtype.	Subtype
process of defining a more general entity type from a set of more specialized entity types.	Generalization
Process of Generalization	Bottom-Up
Process of defining one or more subtypes of the supertype and forming supertype/subtype relationships	Specialization
Process of specialization	Top-Down
An instance of a supertype must also be a member of at least one subtype.	Completeness constraint
Double line	Total specialization Rule (YES)
a rule that specifies that each entity instance of a supertype must be a member of some subtype in the relationship	Total specialization Rule (YES)
Single Line	Partial Specialization Rule (NO)
A rule that specifies that an entity instance of a supertype is allowed not to belong to any subtype	Partial specialization rule (NO)
Whether an instance of a supertype may simultaneously be a member of two (or more) subtypes	Disjointness constraint
An instance of a supertype can be only one of the subtypes	Disjoint rule
An instance of a supertpe could be more than one of the subtypes	Overlap rule
An attribute of the supertype whose values determine the target subtypes	Subtype discriminator
a simple attribute with alternative values to indicate the possible subtypes	Disjoint
a composite attribute whose subparts pertain to different subtypes. each subpart contains a boolean value to indicate whether or not the instance belongs to the associated subtype	Overlapping
model represents data in the form of tables	Relational Data Model
Tables(Relations), Rows, Columns	Data Structure
powerful sql operations for retrieving and modifying data	Data Manipulation
Mechanisms for implementing business rules that maintain integrity of manipulated data	Data Integrity
is a named, two dimensional table of data	Relation
A table consist of_____and _______.	Rows(records), Columns(attribute or field)
able to store and retrieve a row of data in a relation, based on the data values stored in that row.	Relational Keys
every relation must have primary keys	GOAL
an attribute or a combination of attributes that uniquely identifies each row in a relational database	Primary Key
We designate a primary key by underlining the attribute name(s).	TRUE
an attribute in a relation that serves as the primary key of another relation	Foreign Key
We designate foreign key with a broken line	TRUE
Relation must have a unique name	Relation
Every attribute value must be atomic (not multivalued, not composite).	Relation
Every row must be unique (can’t have two rows with exactly the same values for all their fields).	Relation
Attributes (columns) in tables must have unique names.	Relation
The order of the columns must be irrelevant.	Relation
The order of the rows must be irrelevant.	Relation
All relations are in 1st Normal form.	Relation
Other term of Relations	Tables
Relations (tables) correspond with entity types and with many-to-many relationship types.	TRUE
Rows correspond with entity instances and with many-to-many relationship instances.	TRUE
Columns correspond with attributes.	TRUE
The word relation (in relational database) is NOT the same as the word relationship (in E-R model).	The word relation (in relational database) is NOT the same as the word relationship (in E-R model).
The second property of relations listed in the preceding section states that no multivalued attributes are allowed in a relation. Thus, a table that contains one or more multivalued attributes is not a relation.	TRUE
ensure that the data insertion, updating, and other processes have to be performed in such a way that data integrity is not affected.	Integrity Constraints
Allowable values for an attribute	Domain Constraints
The relational data model allows us to assign a null value to an attribute in the just described situations.	Entity Integrity
a value that may be assigned to an attribute when no other value applies or when the applicable value is unknown.	Null
No primary key attribute may be null. All primary key fields MUST have data	TRUE
rule states that any foreign key value (on the relation of the many side) MUST match a primary key value in the relation of the one side. (Or the foreign key can be null)	Referential Integrity
don’t allow delete of “parent” side if related rows exist in “dependent” side	Restrict
automatically delete “dependent” side rows that correspond with the “parent” side row to be deleted	Cascade
set the foreign key in the dependent side to null if deleting from the parent side → not allowed for weak entities	Set-to-Null
E-R attributes map directly onto the relation.	Simple attributes
Use only their simple, component attributes	Composite attributes
Becomes a separate relation with a foreign key taken from the superior entity.	Multivalued Attribute
Becomes a separate relation with a foreign key taken from the superior entity	Mapping Weak Entities
Primary key of identifying relation (strong entity)	Primary key composed
Partial identifier of weak entity	Primary key composed of
one side becomes a foreign key on the many side	One-to-many
new relation with the primary keys of the two entities as its primary key	Many-to-Many
key on mandatory side becomes a foreign key on optional side	One-to-One
Default primary key for the association relation is composed of the primary keys of the two entities (as in M:N relationship)	identifier not assigned
It is natural and familiar to end-users	identifier assigned
Default identifier may not be unique	identifier assigned
One-to-many-Recursive foreign key in the same relation	Mapping unary relationships
has two or more relations	Many-to-Many
primary key has two attributes, both taken from the primary key of the entity	Associative entity
one relation for each entity and one for associative entity	Mapping ternary and n-ary relationships
Associative entity has foreign keys to each entity in the relationship	Mapping ternary and n-ary relationships
One relation for supertype and for each subtype	Mapping Supertype/Subtype Relationships
Supertype attributes (including identifier and subtype discriminator) go into supertype relation	TRUE
Subtype attributes go into each subtype; primary key of supertype relation also becomes primary key of subtype relation	TRUE
1:1 relationship established between supertype and each subtype, with supertype as primary table	TRUE
Primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data	Data Normalization
The process of decomposing relations with anomalies to produce smaller, well-structured relations	Data Normalization
A relation that contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies	Well-Structured Relations
Goal is to avoid anomalies	Well-Structured Relations
adding new rows forces user to create a duplication	Insertion anomaly
deleting rows may cause a loss of data that would be needed for other future rows	Deletion Anomaly
Changing of data in a row forces changes other rows because of duplication	Modification Anomaly
General rule of thumb	A table should not pertain to more than one entity type.
Why do these anomalies exist?	Because there are two themes (entity types) in this one relation. This results in data duplication and an unnecessary dependency between the entities.
The attribute on the left side of the arrow in a functional dependencies	Determinants
an attribute, or combination of attributes, that uniquely identifies a row in a relation.	Candidate Key
For every row, the value of the key must uniquely identify that row. This property implies that each non-key attribute is functionally dependent on that key	Unique Identification
No attribute in the key can be deleted without destroying the property of unique identification.	Non Redundancy
• No multivalued attributes • Every attribute value is atomic • Fig. 4-25 is not in 1st Normal Form (multivalued attributes) ➔ it is not a relation. • Fig. 4-26 is in 1st Normal form. • All relations are in 1st Normal Form	First Normal Form (1NF)
1NF PLUS every non-key attribute is fully functionally dependent on the ENTIRE primary key • Every non-key attribute must be defined by the entire key, not by only part of the key • No partial functional dependencies	Second Normal Form (2NF)
• 2NF PLUS no transitive dependencies (functional dependencies on non-primary-key attributes) - This is called transitive, because the primary key is a determinant for another attribute, which in turn is a determinant for a third	Third Normal Form (3NF)
meaning of SQL	Structured Query Language
The standard for relational database management systems (RDBMS)	SQL
meaning of RDBMS	Relational Database Management System
A database management system that manages data as a collection of tables in which all relationships are represented by common values in related tables	RDBMS
who first developed relational database concept	Edgar F. Cod
when did the relational database concept was developed?	1970
System R with Sequel (later SQL) created at IBM Research Lab	1974-1979
who first market the first relational DB with SQL	Oracle
When did the first market the first relational DB with SQL	1979
Current SQL is supported by most database Vendors	TRUE
• Specify syntax/semantics for data definition and manipulation • Define data structures and basic operations • Enable portability of database definition and application modules • Specify minimal (level 1) and complete (level 2) standards	Purpose of SQL
• Allow for later growth/enhancement to standard (referential integrity, transaction management, user-defined functions, extended join operations, national character sets)	Purpose of SQL
• Reduced training costs • Productivity • Application portability • Application longevity • Reduced dependence on a single vendor • Cross-system communication	Benefits of a standardized relational language
a set of schemas that constitute the description of a database	Catalog
structure that contains description of objects created by a user(base tables, views, constraints)	Schema
Commands that define a database, including creating, altering, and dropping tables and establishing constraints	Data Definition Language (DDL)
Commands that maintain and query database	Data Manipulation Language (DML)
Commands that control a database, including administering privileges and committing data	Data Control language (DCL)
stores string containing any characters in a character set but of definable variable length	VARCHAR (variable character)
stores string containing any characters in a character set to be fixed length	CHAR (character)
Stores binary string values in hexadecimal format	BLOB (binary large object)
stores exact numbers with defined precision and scale	NUMERIC (number)
stores exact numbers with predefined precision and scale of zero	INT (integer)
stores a moment an event occurs	TIMESTAMP
stores a moment an event occurs using a definable fraction of a second precision	TIMESTAMP WITH LOCAL
adjusted to the user time Zone available (available at SQL)	TIME ZONE
Stores truth values	BOOLEAN
browse through many object in a database schema	CONNECTION NAVIGATOR
review the definition of object at a glance	CONNECTION NAVIGATOR
1.Primary tool - SQL DEVELOPER 2. SQL *Plus commandline interface may also be used	TWO DEVELOPMENT ENVIRONMENTS
To enter single single or multiple SQL statement	Use the Enter SQL statement box
code fragments that may be just syntax or examples	Snippets
What to use to describe the command to display the structure of a table	SQL *Plus Commandline
• SAVE filename •GET filename • START filename •@ filename • EDIT filename • SPOOL filename • EXIT	SQL *Plus File Commandline
• CREATE • ALTER • DROP • RENAME • TRUNCATE • COMMENT	Data Definition Language (DDL
Basic unit of storage; composed of rows	Table
Variable-length character data	VARCHAR2(size)
Fixed-length character data	CHAR(size)
Variable-length numeric data	NUMBER(p,s)
Date and time values	DATE
Variable-length character data (up to 2 GB)	LONG
Character data (up to 4 GB	CLOB
Raw binary data	RAW and LONG RAW
Binary data (up to 4 GB)	BLOB
Binary data stored in an external file (up to 4 GB)	BFILE
A base-64 number system representing the unique address of a row in its table	ROWID
You can name a constraint, or the Oracle server generates a name by using the	SYS_Cn format
–constraint that ensures that foreign key values of a table must match primary key values of a related table in 1:M relationships	Referential Integrity
Defines the column in the child table at the table-constraint level	Foreign key
Identifies the table and column in the parent table	References
Deletes the dependent rows in the child table when a row in the parent table is deleted	On Delete cascade
Converts dependent foreign key values to null	On Delete set Null
statement allows you to change column specifications	Alter Table
Use the ALTER TABLE statement to: • Add or drop a constraint, but not modify its structure	Alter Statement
statement allows you to rename an existing column in an existing table in any schema	RENAME COLUMN
• Put a table into read-only mode, which prevents DDL or DML changes during table maintenance • Put the table back into read/write mode	Alter Table
statement allows you to remove tables from your schema. Moves a table to the recycle bin	Drop table
Removes the table and all its data entirely	PURGE DROP TABLE table_name [PURGE]; DROP TABLE CUSTOMER_T;
• Enables you to recover tables to a specified point in time with a single statement • Restores table data along with associated indexes, and constraints • Enables you to revert the table and its contents to a certain point in time or SCN	Flashback Table
• Repair tool for accidental table modifications • Restores a table to an earlier point in time • Benefits: Ease of use, availability, and fast execution • Is performed in place	Flashback Table FLASHBACK TABLE[schema.]table[, [ schema.]table ]... TO { TIMESTAMP \| SCN } expr [ { ENABLE \| DISABLE } TRIGGERS ]
allows you to rename an existing table in any schema	Rename Table(except the schema SYS) RENAME table-name to new- table-name; RENAME employees to emp
• Removes all rows from a table, leaving the table empty and the table structure intact • Is a data definition language (DDL) statement rather than a DML statement; cannot easily be undone	Truncate Statement TRUNCATE TABLE table_name;
You can add comments to a table or column by using the COMMENT statement:	Comments COMMENT ON COLUMN employees.first_name IS 'First name of the employee';
Set of one or more entity	Entity cluster

Created by: 2390141967853775

"Know" box contains:
Time elapsed:
Retries: