Database Past Paper PDF

Q1- Choose the correct answer. ____________ is an organized ,related ,and structured set of data about a particular mini- world. a) Database system _ b) Database management system d) all the answers are true L is a com...

Q1- Choose the correct answer. ____________ is an organized ,related ,and structured set of data about a particular mini- world. a) Database system _ b) Database management system d) all the answers are true L is a computerized system that enables users to create and maintain a database. ) Database management sysem ) Database model a) Database system ¢) Database. The DBMS software together with the data itself and sometimes, the applications are also included is ---------------- X a) Database system ¢) Database b) Database management system d) Database Application. DBMS Facilitates such as. Database administrators DBAs are responsible for a) Creating users ¢) Monitoring efficiency b) Controlling its use d). The history of Data Models may be. a) Object-oriented Network Models c) Object-oriented Models b) Oblectorinted Data Models d) All the answers are true. The problems with Data Redundancy may be a) Wasted Storage Space c) Waste time b) Waste efforts d). A set of concepts to describe the structure of a database ,the operations for manipulating these structures ,and certain constraints that the database should obey. a) Database State ¢) Database Schema b) _ d) All the answers are true. Includes descriptions of the database structure ,data types ,and the constraints on the database. a) Database State b) Data Model d) All the answers are true 10.The actual data stored in a database at a particular moment in time. _ ¢) Database Schema 1/5  b) Data Model d) All the answers are true 11.Describes physical storage structures and access paths a) Conceptual schema b) Extemnal schema d) All the answers are true 12.Describes the various user views. a) Conceptual schema ¢) Internal schema b) d) All the answers are true 13.Describes the structure and constraints for the whole database for a community of users a) Conceptual schema ¢) Intemal schema b) Extemnal schema d) All the answers are true 14 Provides database query and transaction services to the clients. a) File Server b) Schema Server d) All the answers are true 15.Specifics things or objects in the mini-world that are represented in the database c) Attribute b) Key d) All the answers are true 16.Cardinality Ratio may be b) Many-to-onc ) Allthe answersar rue a) One-to-one ¢) One-to- Man 17.Types of Attributes may be a) Simple ¢) Composite b) Multi-valued d) All answers the are true 18.Changes every time the database is updated. a) _ c) Database schema b) Database Entity d) All the answers are true 19.Types of degrees of relationships a) - c) Simple b) One-to-One d) All the answers are true 20.Specifies maximum participation a) _ c) Existence Dependency b) Unary Ratio d) All the answers are true 2/5  Q2- Select T/F 1. Valid State refers to the database state when it is initially loaded into the system. [I 1 2. The duplication of data may lead to data inconsistency. |l 1 3. If the names of students in a university are not duplicated, then the name could be a key. T ] 4. An entity type may have more than one key. [ I 1 5. Entities are those tangible things in the mini-world that are important to be represented in the database. |l ] 6. Conceptual data models provide concepts that describe details of how data is stored in the computer. [F ] 7. Database Schema is the actual data stored in a database at a moment in time. [F ] 8. A Unary Relationship is the relationship between two different Entities. |l ] 9. Key may be simple or composite |l ] 10.The entity sets ,which have a primary key, are a weak entity sets. [F ] Q3- We need to create a database schema design based on the following requirements of the COMPANY Database: [5 Marks] B The company organized into DEPARTMENTs. Each department has a name ,number and an employee who manages the department. We keep track of the start date of the department manager. A department may have several locations. Each department controls a number of PROJECTs. Each project has a unique name ,unique number and is located at a single location. B The database will store each EMPLOYEE’s social security number ,address ,salary ,sex ,and birthdate. Each employee works for one department but may work on several projects. The DB will keep track of the number of hours per week that an employee currently works on each project. It is required to keep track of the direct supervisor of each employee. B Each employee may have a number of DEPENDENTS. For each dependent ,the DB keeps a record of name ,sex birthdate ,and relationship to the employee. Question: Depending on the above scenario, draw the Entity Relationship Diagram by showing the following: 1 All attributes associated with each entity and All required keys 2 All entities and Relationships types 3 The cardinality of association between entity types 4 The participation between entity types 3/5 afs 1. Complete the following: 1.1. Database: is an organized, related, and structured set of data about a particular mini-world 1.2.DBMS: Set of programs to access the data in database system 1.3. Database system is: The DBMS software together with the data itself. Sometimes, the applications are also included. 1.4. Database Users may be divided into Those who actually use and control the database content and who design and develop the DBMS software. 2. DrawA simplified architecture for a database system Database System — ‘Application Programs/Queries DBMS. Software Software o Procass Queries/Programs ! Software to Access. Stored Data 3. Listthe DBMS Facilitates e Define a particular database in terms of its data types, structures, and constraints. e Construct or load the initial database contents on a secondary storage medium. e Manipulating the database: M Retrieval: Querying, generating reports. W Modification: deletions and updates to its content. e Processing and sharing by a set of concurrent users and application programs —yet, keeping all data valid and consistent  e Protection includes system protection against hardware or software malfunction (or crashes) and security protection against unauthorized or malicious access. e 6. Provide Data Recovery services: It is the process of restoring data that has been lost, accidentally deleted, corrupted or made inaccessible. e 7. Provide Database Backup services: It creates a backup files to restore the data if required. e 8. Maintenance of the database and associated programs 4. List Characteristics of Database Approach Self-describing, Insulation, Data abstraction, multiple views, sharing of data data redundancy, data concurrency, integrity constraints, Protecting database 5. What are the problems with Data Redundancy? 1. It may lead to Data Inconsistency. 2. Wasted Storage Space. 3. Waste time and efforts. 4. More Difficult Database Update. 5. Retrieval of data is slow and inefficient 6. Whatis the responsibility of Database administrators DBA? e 1. Creating users and Authorizing access to the database. e 2. Acquiring software and hardware resources. e 3. Controlling its use. e 4. Monitoring efficiency of operations and its use. 7. What s the responsibility of Database designers (Analyzers and Designers)? M Responsible to define the content, the structure, the constraints, and functions or transactions against the database. B They must communicate with the end-users and understand their needs. 8. Listthe history of Data Models: e M Network Model e W Hierarchical Model e W Relational Model e W Object-oriented Data Models e MW Object-Relational Models 1. Complete the following: - 1.1.Data Model is A set of concepts to describe the structure of a database Database Schemais 1.2.Database State is the actual data stored in a database at a particular momentin time 1.3.Valid State is A state that satisfies the structure and constraints of the database. 1.4.the three levels of DBMS schemas are Internal schema, Conceptual schema and External schemas 1.5. Data Independence consists of Logical Data Independence and Physical Data Independence. 1.6.Centralized DBMS is Combines everything into single system includin BMS software, hardware, application programs, and user interface processing software. 1.7.DBMS Server is Relational DBMS servers are often called SQL servers, query servers, or transaction servers. 1.8.Three-tier architecture organizes applications into Presentation, Application and Database Tier. Compare between Database Schema vs.Database State: W The database schema changes very infrequently. B The database state changes every time the database is updated. Explain how Three-tier architecture can enhance security: W Database server only accessible via middle tier. W Clients cannot directly access database server. W Clients contain user interfaces and Web browsers.  Sheet 3 1. Complete the following: - 1.1. Entities are specific things or objects in the mini-world that are represented in the database. 1.2, Weak entity is The entity sets which do not have sufficient attributes to form a primary key 13, key attribute s an entity type for which each entity must have a unique value is called a 1.4. Key may besimple or composite (true/ false) 1.5. Unary Relationship It’s called relationship of degree of 1 1.6. Cardinality Ratio may be (1:1),(1:N).(N:1),(N:N) 2. List the Types of Attributes e Simple e Compose e Multi-valued 3. Why Studying Relationships? Representing facts: All components of a system must interact with each other in some way. If a component not interact with any other component, this means cither: a « It's Tt needs more to discover relationships, or The entity is out of the boundary the system important when dealing with data: Entering Retrieving Deleting Studying relationships may lead to discover new relations 4. List the types of degrees of relationships e Unary e Binary e Ternary e N-ary 5. What are Constraints on Relationship Types (specifies maximum participation) Explain the process to resolve m: n relationships e One-to-one (1:1) e One-to-many (1:N) or Many-to-one (N:1) e Many-to-many (M:N) * (participation) Constraint (specifies minimum participation): e zero (optional participation, not existence-dependent) * one or more (mandatory participation, existence- dependent) Kingdom of Saudi Arabia sg Al ALl Ministry of Education o o ® asdaill )05 g - Bisha University o \ / Ly dady University of Bisha X Complete the following: 1. EER stands for Enhanced ER or Extended ER PA An entity type may have additional meaningful _. _ is the process of defining a set of subclasses of a superclass The set of subclasses is based upon some distinguishing characteristics of the entities in the superclass _ is the reverse ofthe specialization process Several classes with common features are generalized into a superclass In multiple inheritance a subclass can be subclass of more than one superclass. Generalization usually is total (I/F) In practice, we cannot combine both Specialization/Generalization is usually employed (T/F) D Answer the following. Write EER Model Concepts: Additional concepts: Compare between Specialization/Generalization Hierarchies: In specialization, start with an entity type and then define subclasses of the entity type by successive specialization: In generalization, start with many entity types and generalize those that have common properties: Kingdom of Saudi Arabia ol sg Al ALl Ministry of Education denls M1 - r H * s, Bisha Bniispliy how Attribute Inheritha\ fss Relationships: A daala Universityof Bisha o Subclasses inherit all attributes of their superclass. Example: In an EMPLOYEE superclass, subclasses like SECRETARY, TECHNICIAN, and ENGINEER inherit attributes such as Name, SSN, etc. Subclass entities also inherit the relationships associated with the superclass. A SECRETARY entity will have values for the attributes defined in the EMPLOYEE superclass, ensuring that shared characteristics do not need to be redefined in each subclass ‘What are the Constraints on Specialization and Generalization? 1.Disjointness Constraint 2.Completeness Constraint: e Total Completeness e Partial Completeness 4. Compare between Disjointness Constraint and Overlapping Constraint? Disjointness: = Enforces exclusivity among subclasses. Example: An EMPLOYEE specializing into SECRETARY and ENGINEER can belong to only one of these roles. Allows shared membership across subclasses. Example: An EMPLOYEE could simultaneously belong to both SALARIED_EMPLOYEE and MANAGER subclasses. Kingdom of Saudi Arabia A3 ) Ay 1) ASLaal) Ministry of Education o o > asdaill )05 g Bisha University ot Ly daals University of Bisha 5. Explain the types of Completeness Constrain? = Specifies that every entity in the superclass must be a member of at least one subclass. = Specifies that an entity in the superclass does not have to belong to any subclass; it can exist without being a member of any subclass. 6. What are the types of specialization/generalization? e The subclasses are disjoint, and every entity in the superclass must belong to one and only one subclass. e The subclasses are disjoint, but not every entity in the superclass must belong to a subclass. e The subclasses are overlapping, and every entity in the superclass must belong to at least one subclass. e The subclasses are overlapping, and an entity in the superclass may or may not belong to any subclass.  o dacnbo I™ Kingdom of Saudi Arabia - L g &y ) Alaal) Ministry of Education s o Y - il 3,055 Bisha University Umm_.v;;’; Ly Ay Complete the Blanck 1. Relation is a mathematical concept based on the ideas of sets. 2. Avelation looks like a table ofvalues. 3. Arelation typically contains a set of rows. 4. Tn the formal model, rows are called tuples. 5. In the formal model, the column header is called an attribute name or just attribute. 6. The key is uniquely identified for the row in the table. 7. Akey of arelationis a set of one or more attributes that uniquely identify each tuple in the relation. 8. Domain consists of: data type, size. format, logical meaning, and role. 9. Aspecial NULL value is used to represent unknown values or not available or not applicable in certain tuples. 10. Constraints are conditions that must hold on all valid relation states. 11. A super Key isa set of one or more attributes that, taken collectively, identify uniquely a tuple in the relation. 12. If there are several Keys, then they are called candidate keys. 13. AKey is a subset of super keys. 14. There should be only one Primary Key per a table. 15. Primary key could consist of one attribute or composite of multiple attributes. 16. “Primary Key” constraint is used to define the primary key on a column. 17. The rest of the candidate keys that have not been chosen to be the primary key are called secondary keys. 18. Keys - in general - ensure uniqueness, but do not ensure NOT NULL values. True or false Questions: A relation must have only one key. (F) The tuples are not considered to be ordered, even though they appear to be in the tabular form. (T) Order of columns is necessary. (F) Order of rows is insignificant. (T) Any key is a super key. (T) Primary Key is not one of the candidate keys. (F) It is impossible to have more than one secondary key in a table. (F)  o dacnbo I™ Kingdom of Saudi Arabia - L g &y ) Alaal) Ministry of Education o M ° - sl 555 Bisha University Lot iy daaly Universityof Bisha Question 3: Answer the following 1. How to retrieve data effectively? Data can be retrieved effectively using SQL queries and database management systems (DBMS), often with the help of indexing. 2. List the main types of constraints that can be expressed in the relational model. o Key constraints o Entity integrity constraints o Referential integrity constraints o Domain constraints 3. What is the Role of a Primary Key? The primary key uniquely identifies each row in the table, prevents duplicate data, and is used to establish relationships between entities. It also allows the DBMS to build an index for efficient data retrieval. 4. List the General rules for Choosing Primary Keys. o Must not contain NULL values. o Should have values controlled fully by the database designer. o Should be stable and not change frequently. o Prefer smaller and simpler keys (single attribute over composite).