Karteikarten
MGTS3203
Karten in diesem Stapel (69)
- 1Lecture 1 (database)database system is a computerized record keeping system (functions: adding new tables, inserting data (records, rows), retrieving data, changing (updating) data, deleting data,
- 2Other database functionsDefining views (subschemas)
granting and enforcing privileges
defining and enforcing integrity constraints
providing concurrency controls - 3Data Hardware Software UsersIntegrated, shared
Secondary storage, processor, memory
DBMS, application programs
End users, application programmers, database administrator - 4DBA (database administrator)creates and monitors database
- 5DA data adminstratorunderstands data needs of organisaion. Decide what data should be stored and collected - data quality
- 6Entity Relationships, PropertiesE - distinguishable object
R - linkages between entities
P - attributes - 7Problems with file-based systemsseparation and isolation of data
duplication
data dependence
incompatible file formats
proliferation of programs - 8ACIDAtomicity all or nothing (all of transaction must be executed or none of it)
Consistency
Isolation
Durability - 9Lecture 13 Exam Preview Data modeling ERD (10)Drawn an implementation ERD - represent data you wish to collect. Follow cardinals, primary keys and foreign keys (do not need to list all attributes in each of the entities)
- 10Data Modelling Logical DFD (6)Write the SQL create statement for one or two of the major entities from your ERD
- 11Data integrity (10)Describe the integrity constraints, including domains, that you would use to ensure data integrity in one or more of the tables represented in your ERD 1/2 page
- 12Data Security and Recovery (8)Describe security and recovery plans for your ERD/database
- 13Interesting and Useful Queries (21)Based on ERD - 3 queries (with explanations)
- 14Data Warehousing - (7)explain how you would approach creating a data warehouse using the system you created (previous questions) plus other data that may be available from other sources (local councils, industry bodies, government departments)
- 15Data mining (8)Explain how the data warehouse differs from the OLTP system of earlier questions. Data structure and types of queries users would address to each system
- 16Basic OO conceptsNeed to add this later
- 17Distributed Databasesconsists of a collection of sites connected via a communications network, in which:
a - each site is a full database system
b - a user at any site can access data anywhere in the network exactly as if the date were in their own site - 18Goal of DDapps should operate in different DB's, managed by different DBMS, different machines, different OS, different networks
- 19Motivation for DDconnect, match info structure, local control, global access, efficiency, accessibility
- 20Advantages of DDOrganisational structure - DD permit local organisational subunits to have optimal response time. Information structure matches organisational structure
- 21cont.shareability and local autonomy - better response times for local must ensure consistent domain and attribute naming conventions, lengths, data, types for global
- 22cont.improved availability and reliability - multiple communication paths can exist (multiple sites) failure of one site may not make entire system inoperable
Improved performance - data subsets (distributing data improves by partitions) - 23cont.modular growth - new sites can be added with minimal effects on existing sites
- 24DisadvantagesComplexity (networking, coordinating)
cost
security (decentralized systems harder to secure) - 25cont.integrity control more difficult
database design more complex
personnel - 26Twelve objectives of DDLocal autonomy - local site can do everything central site can
No reliance on central site (because - bottleneck, vulnerable) - 273,4,5continuous operation (reliability, availability)
Location independence (users shouldn't need to know where data is physically stored)
Fragmentation independence (support data fragmentation can be divided for storage purposes - 286,7, 8Replication independence (same data can be stored at multiple sites (performance boost)
distributed query processing (relational systems outperform other systems, optimization becomes more important
Distributed Transaction management - 299,10,11,12hardware independence
OS independence
Network independence
DBMS independence - 30Problems with DDquery processing, catalog management, update propagation, recovery control, concurrency control, two-phase locking, two phase commit
- 31Lecture 11 Securityprotection against internal, external and natural (theft, fraud, breach of confidentiality, privacy, data integrity)
- 32Security vs IntegrityS - protecting database from unauthorised access
Integrity - accuracy and validity of data
Relative to data input (field checks, record checks, domain constraints, two-phase locking, two-phase commit and COMMIT and ROLLBACK - 33SecurityIdentify security threats to IS, expected losses, and action plans
other factors - legal, social and ethical aspects - 34Security considerations Physical controls Hardware Controls Operating SystemP - Uninterruptible power sources, physical equipment
H - server security for restricting accrss
O - recovery log, system flush main memory - 35Operational considerations Policy Questions Disaster Recovery PlanO - personnel selection, training, scheduling
P - how does management decide the general rules for determine who is authorised to what - 36Security controls DBauthorization, discretionary control, mandatory control (objects tagged with specific classification levels)
- 37Audit trails and Log filesFor all non-trivial DB the DB should record all attempted accesses whether successful or not (queries should be stored, update access should store before and after images)
efficiency purposes
collecting data - 38Log files containrequest
terminal
user
date-time
portion of DB affected
old data
new data values - 39Cold and Hot sitescold site contains replacement hardware, hot site duplicates the operations of the primary site (e.g all transactions)
- 40Encryption of data requiresan encryption key (origin)
encryption algorithm
decryption key (receiving)
decryption algorithm applied to decryption key - 41Audits conducted1 to verify that the security of the database is being maintained
2. maintain and improve accuracy of data - 42Security QueriesRequests made by users not authorized (count the attempts)
- 43Lecture 10 (Transaction management: recovery concurrency)Possible threats both accidental and deliberate damage databases.
- 44Threats:two programs might interfere with one another's operations, data could be read by unauthorized, updates may ruin the system, system crash, damaged
- 45Controls to protect databaseconcurrency, recovery, security, integrity
- 46COMMIT ROLLBACKC - signals successful end of transaction
R - signals an unsuccessful end of transaction - 47ConcurrencyDBMS permit different processes to update different portions of the database at the same time
- 48Lost update problemTransaction A overwritten by trans b
- 49Goals for DB transaction acidAtomicity (transaction entire or not at all)
Consistency (end state should be consistent with origin state
Isolation - transaction execute independently (partial effects should not be visible) not until commit
Durability - transactions must not be lost - 50LockingS - shared lock
X - exclusive lock (only one) - 51Two phase locking1 acquiring locks
2 releasing locks
Can never have additional locks after first lock - 52Levels of IsolationRepeatable read - all locks are held until end of transaction
Cursor stability - transaction acquires S-lock then loses address-ability without updating or promoting to x-lock then lock is released - 53Lecture 9 DSS Operational decisionsday-to-day (transaction)
- 54DSS Tactical decisionsbroader - external infromation is often desirable (use data marts
- 55DSS strategiclong term (placement within industry)
- 56Data cleaning Single table Multi tablesreasonableness between attributes, sign, ranges, relationships.
M - referential integrity, masterfile refernce - 57Lecture 8 GGJust SQL
- 58Lecture 7domain is a set of legal scalar values for attribute (coloumn)
- 59Five types of integrity rules1. candidate keys
2. foreign keys
3. nulls
4. domain constraints
5. business rules - 60Referential integritycorresponding record must exist in another table
- 61Relationsheading and a body (body consists of rows 'records')
- 62Field checks and domain constraintsfield checks - examine values of individual attributes (Field checks and domain constraints = same)
- 63Data type restrictions Not null Limit or rangeDTR - only certain types of values
N - must have a value
L or R -Max-min
CC - limit number of characters - 64Record checks CompletenessChecking that values aren't null at different stages
- 65Lecture 6 ViewVirtual table only exists via query
- 66Lecture 5 Normalization 1nFNo two rows of data must contain repeating group of information
- 672nfmust not be any partial dependency of any column on primary key. Each column that is not part of the PK must depend on the concatenated key for its existence
- 683NFevery non-prime attribute must be dependent on the primary key (Example student id, street, city, state, zip)
street, city, state are dependent on zip - 69Lecture 4 (crow)
Informationen
- Beschreibung
- Karteikarten am MGTS3203, erstellt von joel_clement34 am 03/11/2015.
- Erstellt von
- joel_clement34
- Aufrufe
- 0
- Tags
- Bewertung