click below
click below
Normal Size Small Size show me how
DatabaseEmergency
database systems
Question | Answer |
---|---|
3 kinds of schema, what are they? | External, internal, conceptual |
describe external schema | view closest to users. User view; shows what a particular user group is interested in and hides the rest of the DB from them |
describe conceptual level | describes the database for a community of users; hides details of physical storage, contains constraints, data types, relationships, user operations and entities; created by the DBA |
what's data independence | Logical independence, you can change up the conceptual schema without having to change the external schema or application programs |
what's physical independence | you can change the physical (internal level) without changing the conceptual schema |
candidate key | If you have 2 keys, choose one as the primary and the other is the candidate key which could potentially switch roles with the original primary key |
primary key | unique & non, redundant, that distinguishes tuple from table (can have single or multi variable attributes) |
foreign key | not a key but an attribute so a connection can be made |
Entity Integrity constraint | no primary key value can be NULL |
referential integrity constraint | maintains consistency between 2 tuples |
3 operations on a DB | Insert Delete Update |
Insert's constraint violations | Domain integrity Key integrity violation Entity integrity violation Referential integrity violation |
Domain integrity violation | wrong data type or not in this defined domain |
key integrity violation | duplicate key in this table (two same SSN's) |
Entity integrity violation | Primary key has a null value |
referential integrity violation | foreign key of new entity doesn't have a primary key to reference |
Delete violates only this integrity | referential integrity (you got rid of a tuple that had a primary key someone was looking at) |
what is done when delete violates referential integrity? | 1 Reject the deletion 2 cascade 3 set the refernced tuple to NULL or default |
Update's violations are... | the exact same as INSERT's |
define a view | virtual/derived/not-real table that can only be built on the base table |
Technical factor to consider in choosing a DB | 1 DDl's and DML's must be compared 2 Storage of the system must be compared 3 Access path to know how the data will be retrieved 4 user interface 5 Backup & recovery procedure 6 Security/Integrity 7 Built-in-functions to make programming easier |
define normalization | process of analyzing relation schema to get 1 minimized redundancy 2 minimize the insertion, deletion and update anomalies |
1st normal form | the only attribute values are atomic (pretty much means each cell only has a single value & no duplicate rows) |
2nd normal form | everything that's in the relational table is completely dependent on the primary key of that table |
3rd normal form | 2nd normal form but no attributes are transitively dependent on a relational primary key from the same table (see p524 in textbook if this is confusing) |
3 ways to store on a disk | sequential organization direct organization Indexed sequential |
Sequential Organization | stored one after the other with sequential access |
direct organization | the disk address & data ID have direct access |
indexed sequential | looks at groups of addresses one after another |
3 things to know about physical design | 1 file organization (see ways to store on disk) 2 choices to improve response time 3 factors that influence physical DB design |
Things to improve retrieval | 1 Response time 2 throughput 3 space utilization |
Factors that influence database designs | 1 Queries/transactions-what kind of queries 2 Analyzing the expected freq of the queries-how often is a query used 3 Analyzing the time constraint 4 Analyzing the expected frequency of updated operations 5 Analyzing the uniqueness of analyzed attribut |
Describe internal schema | Describes the physical storage structure of the DB which includes data storage and access paths |