Download SQL Notes which includes basic to advance level Queries and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!
PimpriChinchwad Education Trust’s
Pimpri Chinchwad College of Engineering (PCCoE)
(An Autonomous Institute) Affiliated to SavitribaiPhule Pune University(SPPU) ISO 21001:2018 Certified by TUV SUD
Question Bank
Department: Computer Engg. Academic Year: 2024- 2025 Semester: II Class: Second Year B.Tech. Subject: Database Management Systems Q. No Unit_ No Question 1 1 Suppose you are given the following requirements for a simple database for the National Hockey League (NHL): the NHL has many teams, each team has a name, a city, a coach, a captain, and a set of players, each player belongs to only one team, each player has a name, a position (such as left-wing or goalie), a skill level, and a set of injury records, a team captain is also a player, a game is played between two teams (referred to as host_team and guest_team) and has a date (such as May 11th, 1999) and a score (such as 4 to 2). Construct a clean and concise ER diagram for the NHL database. Ans :-
A university registrar’s office maintains data about the following entities:
- courses, including number, title, credits, syllabus, and prerequisites;
- course offerings, including course number, year, semester, section number, instructor(s), timings, and classroom;
- students, including student-id, name, and program;
- instructors, including identification number, name, department, and title. Further, the enrollment of students in courses and grades awarded to students in each course they are enrolled on must be appropriately modelled. Construct an E-R diagram for the registrar’s office. Document all assumptions that you make about the mapping constraints. Ans :- 3 1 Construct an E-R diagram for a car insurance company whose customers own one or more cars each. Each car has associated with it zero to any number of recorded accidents. Car insurance tables: person (driver-id, name, address) car (license, year, model) accident (report number, date, location) participated(driver-id, license, report-number, damage-amount) Ans :-
Design an E-R diagram for keeping track of the exploits of your favorite sports team. You should store the matches played, the scores in each match, the players in each match and individual player statistics for each match. Summary statistics should be modelled as derived attributes. Ans :- 6 1 Consider a university database for the scheduling of classrooms for - final exams. This database could be modeled as the single entity set exam, with attributes course-name, section-number, room-number, and time. Alternatively, one or more additional entity sets could be defined, along with relationship sets to replace some of the attributes of the exam entity set, as course with attributes name, department, and c-number section with attributes s-number and enrollment, and dependent as a weak entity set on course room with attributes r-number, capacity, and building. Show an E-R diagram illustrating the use of all three additional entity sets listed. Ans :-
Construct an ER Diagram for Company having following details : Company organized into DEPARTMENT. Each department has unique name and a particular employee who manages the department. Start date for the manager is recorded. Department may have several locations. A department controls a number of PROJECT. Projects have a unique name, number and a single location. Company’s EMPLOYEE name, ssno, address, salary, sex and birth date are recorded. An employee is assigned to one department, but may work for several projects (not necessarily controlled by her dept). Number of hours/week an employee works on each project is recorded; The immediate supervisor for the employee. Employee’s DEPENDENT are tracked for health insurance purposes (dependent name, birthdate, relationship to employee) Ans :- 8 1 A bookstore wants to store information about books, authors, publishers, and genres. An author can write multiple books, and a book can have multiple authors. A publisher can publish multiple books, and a book can be published by multiple publishers. Construct an extended ER diagram to represent this scenario, including weak entities, supertypes, and subtypes if necessary.
Draw ER model of university database application considering the constraints − A university has many departments. Each department has multiple instructors (one person is HOD). Here the HOD refers to the head of department. An instructor belongs to only one department. Each department offers multiple courses, each subject is taught by a single instructor. A student may enroll for many courses offered by different departments. Ans :- 11 1 Create an ER diagram for a social media platform. The system includes the following entities: User, Post, Comment, and Like. Design an ER diagram illustrating the
relationships among these entities, along with their attributes and appropriate cardinality constraints. Ans :- 12 1 Compare the benefits of using a DBMS versus a manual file system for managing large volumes of data. Ans :-
taken considering mapping cardinality is one to one, one to many, many to one or many to many. Explain in detail the decision taken during the above situations. Ans :-
Relations Corresponding to Aggregation
- To represent aggregation, create a table containing o primary key of the aggregated relationship, o the primary key of the associated entity set o Any descriptive attributes
Describe the three levels of data abstraction in a DBMS. Ans :- Physical or Internal Level It is the lowest level of data abstraction which defines how data is stored in database. It defines data structures used to store data and methods to access data in database. It is very complex to understand and hence kept hidden from user. Database administrator decides how and where to store the data in database. Physical level deals with actual storage details like data organization, disk space allocation and data access methods. Logical or Conceptual Level It is intermediate level present next to physical level. It defines what data is present in database and their relationships between them. It is less complex as compared to physical level. Programmers generally work at this level and depending on data, structure of tables, relationships and their constraints is decided at this level. View or External Level It is the highest level in abstraction. There are different levels of views and each view defines only a part of whole data required to user. This level defines many views of same database for simplification of view to user. This is the highest level and easiest to understand for user.
o Challenge: Handling superclasses and subclasses. You can use different strategies such as single-table, class-table, or concrete-table inheritance. o Consideration: The choice of strategy affects normalization and performance.
- Multivalued and Composite Attributes: o Challenge: Multivalued attributes (e.g., multiple phone numbers) need a separate table, while composite attributes (e.g., address) are broken into individual columns. o Consideration: Proper mapping ensures data consistency and efficiency.
- Weak Entities: o Challenge: Weak entities depend on strong entities for identification, so the weak entity table includes the strong entity's primary key as part of the composite key. o Consideration: Ensure referential integrity between related entities.
- Relationships: o One-to-One (1:1): Foreign key can be placed in either table. o One-to-Many (1:N): Foreign key is placed in the "many" side. o Many-to-Many (M:N): Requires a junction table with foreign keys from both entities. o Consideration: Correct foreign key placement ensures referential integrity.
- Normalization vs. Denormalization: o Challenge: Normalization removes redundancy, but sometimes denormalization is necessary for performance. o Consideration: Balance between data integrity and performance. By addressing these challenges, you can convert EER diagrams into an effective relational schem 21 1 Reduce following the E-R schema to a table
Ans :- 22 1 How the relationship between different entities can be shown using Mapping Cardinalities constraint in ER Diagram? Explain with Example. Ans :- Q13 same 23 1 For the database system to be usable, it must retrieve data efficiently. The need of efficiency has led designers to use complex data structures to represent data in the database. Developers hide this complexity from the database system users through several levels of abstraction. Explain those levels of abstraction in detail. Also explain the corresponding schema design at each level of abstraction. Ans :- Q15 same 24 1 What Decisions are taken for reducing following components of ER Diagram into tables:
- Entity Set 2. Relationship set 3. Composite and Multi valued attributes Explain with suitable example. Ans :-
