4262678
| Question | Answer |
| Drawbacks of file based databases? | -Data redundancy -Data inconsistency -Data inconsistency - when the system's applications find it difficult to make updates/retrievals of data from multiple files -Data integrity - it is difficult to alter system applications to implement new constraints, and the application programs require that the input/stored data to meet certain consistency criterias -Data security- there are issues with accessing level privileges as well as implementing these constraints can be difficult -Management of concurrency access - on File based systems, files are locked when a user is using it |
| How do RDBMS attempt to correct file based systems' weaknesses | -Unitary storage in one software -Reduced data inconsistency due to data minimisation -Links can be used to refer to certain tables instead of repeating that data -Reduced data duplications -> reduced quantity of data stored -> faster database access speed -Concurrency management () |
| What is a logical schema and what is it intended for? | A diagrammatic illustration of the structure of a database's model, showing how items such as tables, described accordingly to the database MS' specifications, |
| How do data dictionaries in DBMS help address data management issues found in file based systems? | Data dictionaries contains: -detailed description of each piece of data -relationships between data -Access levels -Rules for Validations -Protocols for recovering data -A map to distinguish between logics and physics - intended for storing -A log of transactions for monitoring |
| What are developer Interfaces and Query processors and why are they used? | Developer Interface: Allows graphical manipulation of database MS' features so it is more convenient for non-developers Query Processor: handles high level language queries by compiling or interpreting. It parses (syntactically analyse), optimises and validates |
| What is referential integrity, secondary key and indexing? | Prevention of entrance of inconsistent data Secondary key: candidate keys not chosen to be PKs Indexing: the duplication of various columns of data, can be low level disk block addressing or complete duplications, that allows searches, lookups and queries to be performed more quickly. |
| Criterias for 1NF, 2NF and 3NF? | 1NF: data atomicity, no repeated group of attributes 2NF: 1NF and no partial dependency 3NF: 2NF and no non-key dependency |
| What's the difference between Data Definition Language (DDL) and Data Manipulation Language (DML). | DDL: used to define the overall database structure (creation, modification), using commands like ALTER, CREATE DML: Used for data maintenance, with commands like SELECT |
| Create database syntax? | CREATE DATABASE <dbname> |
| Create table syntax? | CREATE TABLE <table name> ( <field name> <field type(size)>; ... PRIMARY KEY (<field name>)) |
| Changing table definition syntax? | ALTER TABLE <table name> ADD <field name> <field type(size)> ALTER TABLE <table name> DROP COLUMN <field name> ALTER TABLE <table name> ALTER COLUMN <column name> <new file type> |
| Add a primary key after table definition? | ALTER TABLE <table name> ADD PRIMARY KEY (<field name>) For non-null constraint addition of pk where pk is previously null: ALTER TABLE <table name> ADD CONSTRAINT <field name1> PRIMARY KEY (<fieldname2>,<fieldname3>) |
| Group by functions? | Are functions that group the results by a column GROUP BY (column1,column2,...) |
| Syntax for inner join? | Selects all fields from both tables as long as there is one field that's the same on both table SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column1=table2.column2; |
| what do the fields after the SELECT clause represent? | fields that will be shown in the result. |
| INSERT INTO syntax | INSERT INTO tablename (column1,column2,column3,...) VALUES (value1,value2,value3,...); |
| DELETE FROM syntax? | DELETE FROM table_name WHERE some_column=criteria; |
| UPDATE syntax? | UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=criteria; |
| How do DBMS avoid data clashes | Record locks - locking a record as soon as it is modified, it can only be further modified by the same/different user if acknowledged by the first user that that data needs editing -Serialisation - scheduling transactions (e.g. reading or writing) of data in a serial order -Timestamps - each transaction has a timestamp that indicates when that record was last transacted, and is used by the DBMS to determine whether or not the data should be transacted Commitment Ordering - looks at the priority and/or the impact of a transaction on a database and makes a decision on when to permit that transaction |
Want to create your own Flashcards for free with GoConqr? Learn more.