Micah Acinapura Database Systems Midterm Exam 1. A RDBMS is a piece of software to be installed on a computer that manages the data base. The RBDMS is responsible for things like transaction management, parseing and executing queries, and basically all the interaction from the users to the database. 2. Faster, more intuitive, and based on real mathematical calculation. 3. A data model is a discription of how the data from the real world in to be represented in the data base. This is done by way of creating schemas. 4. Entities, their attributes, and their relationships between eachother. 5. The physical schema is a discription of how the data is actually stored on the disk. This provides the abstraction to the user of how the dataa is stored, allowing the DBMS to change it when necessary, without the users knowing the difference. (i.e. how files are stored in the file system) The conceptual schema is similar in that it provides an abstraction of how the data is organized in the data base. (i.e. what data is stored in which tables and how those tables represent the data being modeled). The external schema is a discription of how the data is presented to the users. This lets the users abstract away from how the DMBS is representing the data in tables, and lets them only deal with the data in the way it is presented to them. (i.e Views). 6. Physical data independance in directly related to, or provided by, the physical schema. Physical data independance is the idea that, the way the data is stored on the disk is independant of the way users see it. Logical data independance is very similar except that instead of providing independance on how the data is stored on the disk, Logical data independance is the idea that, how the data is stored in tables within the database, can be independant of how the end-users will actually be presented with the data. 7. A transaction is "one logical unit of access to the database." Basically it is one insert,update,select or delete from the database or any other call that requires information from the database. 8.A locking protocal is a method followed by each application that involves locking off certian rows, or tables, so that two users can't access the same data at the same time. While the locking protocal stops concurrent access, it is designed so that access to the database will appear concurrent through an interleaving process. 1)Two phase locking protocal 2)? 9. The WAL or write ahead log is a record of all the transaction occuring in the database. However the WAL records transactions as they come in, before they are executed, thus each time a transaction is sent to the DBMS it is put into the WAL waiting to be executed. If a crash occurs the WAL log is a good way of seeing what was going on when the system was crashed and which transiction weren't completed that need to be. 10. 11. An entity is the object in the database that is supposed to represent something in the real world that you are trying to model. Entity AKA Table AKA Relation 12. An attribute is linked to an entity and represents a piece of information that you wish to store about that entity. Attribute AKA Colunm 13. A relation is an entity or an object that has information and is linked to other objects or relations. A relational schema is a discription of that object and that data that is stores. A relation instance refers to a specific table with specific data in it. A relation is like an empty table that can be used as a template for a relation instance. 14. A key is a value or set of values that identify a touple in the relations. A candidate key is the a minimal set of attributes that UNIQUELY identify a touple in the relation. A primary key is the candidate key that was chosen for a specific relation. A foreign key is an attribute in one relation that exists in another one. A super key is the set of all attributes of a relation. 15. The domain of an attribute (which is where we define domain) is the type of possiable values that the giver attribute can hold. Not the actual values the attribute can hold but the type. (i.e. int, char, text, NOT 23, 'a', 'hello') 16. A touple is a group of information in the a specific relation that has data for each attribute, a touple is referenced by its primary key value. Touple AKA Row 17. DDL/DML/??L DDL or the Data Definition Language are the SQL statements responsible for creating the schemas of the database. (i.e. add/drop tables, add/drop constraints). DML or the Data Manipulation Language are the SQL statements that access the data in some way. Any insert, delete, etc. command is part of the DML. Basically these are the statements that will change or retrieve information from the database. Transactions all come from DML statements. ??L I forgot what the last one was but if it were up to me I would seperate out SELECT statements in to a DAL, or data access language. but i guess they are similar enought to insert,update,delete to be part of the DML. 19. Primary and Foreign key constraints are one group of constraints. A primary key constraint is a constraint on a attribute (or set of attributes) saying that each value in these attributes must be unique and not null. A foreign key constraint on a attribute limits values in the attribute to values contained in the table the foreign key references. 20. Check constraints and Asserts are two more type of constraints. Check constraints make sure a certian condition is true before data can be entered into a specific attribute. Asserts are the same except they can be used for more then one attribute. 21. Three other types of IC's are domain constraints, not null constaints, or unique constraints. A domain constraint restricts what types of data an attribute can hold. A not null constraint limites to data in the attributes to having values, and not being filled with the NULL marker. Unique constraints limit information in a attribute to not being the same asthe data in the same attribute of another touple in the same table. 22. A view is a table that is not actually stored in the database but rather materalized from the view definition (usually an sql statement) once a user requires that view. Views are used for creating the external schema, specifically restricting what data certian users can see or change. 23. 24. 25. 26. 27. This has to do with the idea the the return value of all relational operators is a set, as well as the input for all operators being a set (or two sets for binary operators). Thus the output from one operator can be used as the input for another operator, thus composing them. 28. A trigger is a action that is to be performed once a specificed change is made to the database. There are two kind of trigger, pre and post, for each of three groups, insert, update, or delete, making six different types of triggers. Triggers are used for two things, the "ugly" use is using them for integrity constraints on data. The nice use of triggers is for record keeping. (i.e. when something is changed in one table, the trigger is fired and information is put into another table). 29. Phase 1: activation - pre/post insert/delete/update - decides when the trigger is to be called Phase 2: firing - when(this = that) - this is the conditional part that decides when the trigger is going to actually do its stuff. Phase3: Action - this what the trigger does once it has been fired, be it insert data into another table or raise and exception or some other stored procedure. 30. Like I was saying earlier, triggers can be used to enforce integrity constraints, but it seems that this is only a coinsedence of their creation. To me it seems like trigger were created for keeping records and then poeple said "hey heres and easy way to add integrity constraints with out having to alter tables." Trigger are a usefull tool for tracking changes within the database but seem to be overused.