RDBMS-Assignment-1

1. Atomicity of updates
   For example, it can ensure that fund transfers between accounts are either successfully completed or not executed at all. In database systems, this guarantee can prevent situations where funds are deducted for some reason but not added to another account, thereby avoiding data inconsistency and financial losses. File systems lack such guarantees, making inconsistencies more likely in such transactions.
2. Concurrent access by multiple users
   For example, database systems enable multiple students to enroll in courses simultaneously, enhancing system performance. However, without proper management, uncontrolled concurrent access may lead to issues like overbooking courses, causing frustration among students.
3. Security problems
   For example, in a healthcare database, it's vital to control access to patient records. Database systems offer fine-grained access control, allowing restrictions based on roles. For instance, doctors and nurses can access medical records, but administrative staff cannot. Achieving this control is challenging in a file system, posing security risks.

1. Conceptual Design
2. Logical Design
3. Optimization
4. Physical Design

• A schema in the context of databases is a structured blueprint that defines how data is organized, relationships are managed, and constraints are applied within a database system.

• The set of special attribute(s) is called key.
• Formally, a key of an entity set is a set of attributes that can uniquely identify the entities.

1.Find the major number of the student Eva:

SELECT major
FROM student
WHERE sname='Eva';
Copy

2.Find the name of the professors (instructors’ position) who are from the Computer Science department:

SELECT iname 
FROM instructor
WHERE department='Computer Science';
Copy

3.Find the name of the professors (instructors’ position) who are from the Computer Science department:

SELECT cname
FROM course
WHERE credit=1;
Copy

4.Find the name of the students who have received an A in a course in semester 2023 Spring:

SELECT sname 
FROM student, enroll
WHERE enroll.grade = 'A'
AND enroll.semester = '2023 Spring'
AND student.sID = enroll.sID;
Copy

5.Find the name of the courses which are instructed by Dave (instructor’s name):

SELECT cname
FROM course,instructor,teach
WHERE instructor.iname = 'Dave'
AND instructor.iID = teach.iID
AND teach.cID = course.cID;
Copy

6.Find the name of the instructors who have taught Frank (student’s name) in semester 2022 Fall:

SELECT iname
FROM instructor,student,teach,enroll
WHERE student.sname = 'Frank'
AND student.sID = enroll.sID
AND enroll.cID = teach.cID 
AND teach.semester = '2022 Fall'
AND teach.iID = instructor.iID
Copy