1"""
2ER Model to Relational Schema Mapping
3
4Demonstrates the 7-step ER-to-relational mapping algorithm:
5
61. Regular entity types → relations
72. Weak entity types → relations (include owner's PK)
83. 1:1 relationships → foreign key in either side
94. 1:N relationships → foreign key on N side
105. M:N relationships → separate relation with both PKs
116. Multivalued attributes → separate relation
127. N-ary relationships → separate relation with all PKs
13
14Theory:
15- ER model is conceptual (design phase)
16- Relational model is logical (implementation phase)
17- Mapping preserves semantics and constraints
18- Choice of where to place FK in 1:1 depends on participation constraints
19"""
20
21import sqlite3
22from datetime import date
23
24
25def create_university_er_model(conn: sqlite3.Connection):
26 """
27 Create a university database following ER-to-relational mapping.
28
29 ER Design:
30 - Entities: Student, Course, Department, Professor
31 - Relationships:
32 * Student -[Enrolls]-> Course (M:N)
33 * Department -[Offers]-> Course (1:N)
34 * Professor -[Teaches]-> Course (1:N)
35 * Professor -[Chairs]-> Department (1:1)
36 * Student -[Advises]- Professor (M:N)
37 - Weak Entity: Dependent (depends on Student)
38 - Multivalued: Student.phone_numbers
39 """
40
41 cursor = conn.cursor()
42
43 print("=" * 60)
44 print("STEP 1: REGULAR ENTITY TYPES → RELATIONS")
45 print("=" * 60)
46 print()
47
48 # Regular entity: Student
49 print("Entity: Student(student_id, name, dob, gpa)")
50 cursor.execute('''
51 CREATE TABLE Student (
52 student_id INTEGER PRIMARY KEY,
53 name TEXT NOT NULL,
54 dob DATE,
55 gpa REAL CHECK(gpa BETWEEN 0.0 AND 4.0)
56 )
57 ''')
58 print("✓ Created table Student\n")
59
60 # Regular entity: Course
61 print("Entity: Course(course_id, title, credits)")
62 cursor.execute('''
63 CREATE TABLE Course (
64 course_id TEXT PRIMARY KEY,
65 title TEXT NOT NULL,
66 credits INTEGER CHECK(credits > 0)
67 )
68 ''')
69 print("✓ Created table Course\n")
70
71 # Regular entity: Department
72 print("Entity: Department(dept_id, name, building)")
73 cursor.execute('''
74 CREATE TABLE Department (
75 dept_id INTEGER PRIMARY KEY,
76 name TEXT NOT NULL UNIQUE,
77 building TEXT
78 )
79 ''')
80 print("✓ Created table Department\n")
81
82 # Regular entity: Professor
83 print("Entity: Professor(prof_id, name, salary, dept_id)")
84 cursor.execute('''
85 CREATE TABLE Professor (
86 prof_id INTEGER PRIMARY KEY,
87 name TEXT NOT NULL,
88 salary REAL CHECK(salary > 0),
89 dept_id INTEGER,
90 FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
91 )
92 ''')
93 print("✓ Created table Professor\n")
94
95 print("=" * 60)
96 print("STEP 2: WEAK ENTITY TYPES → RELATIONS")
97 print("=" * 60)
98 print()
99
100 # Weak entity: Dependent (depends on Student)
101 # PK includes owner's key (student_id) + partial key (dependent_name)
102 print("Weak Entity: Dependent(student_id, dependent_name, relationship)")
103 cursor.execute('''
104 CREATE TABLE Dependent (
105 student_id INTEGER,
106 dependent_name TEXT,
107 relationship TEXT, -- 'spouse', 'child', etc.
108 PRIMARY KEY (student_id, dependent_name),
109 FOREIGN KEY (student_id) REFERENCES Student(student_id)
110 ON DELETE CASCADE
111 )
112 ''')
113 print("✓ Created table Dependent (weak entity)")
114 print(" - Composite PK: (student_id, dependent_name)")
115 print(" - student_id is FK to Student\n")
116
117 print("=" * 60)
118 print("STEP 3: 1:1 RELATIONSHIPS → FOREIGN KEY")
119 print("=" * 60)
120 print()
121
122 # 1:1 relationship: Professor chairs Department
123 # FK can go on either side; we choose Professor side
124 print("Relationship: Professor -[Chairs]-> Department (1:1)")
125 cursor.execute('ALTER TABLE Department ADD COLUMN chair_id INTEGER')
126 cursor.execute('''
127 CREATE UNIQUE INDEX idx_dept_chair ON Department(chair_id)
128 ''')
129 print("✓ Added chair_id to Department (UNIQUE ensures 1:1)")
130 print(" - Each department has at most one chair")
131 print(" - Each professor chairs at most one department\n")
132
133 print("=" * 60)
134 print("STEP 4: 1:N RELATIONSHIPS → FOREIGN KEY ON N SIDE")
135 print("=" * 60)
136 print()
137
138 # 1:N: Department offers many Courses
139 print("Relationship: Department -[Offers]-> Course (1:N)")
140 cursor.execute('ALTER TABLE Course ADD COLUMN dept_id INTEGER')
141 cursor.execute('''
142 CREATE INDEX idx_course_dept ON Course(dept_id)
143 ''')
144 print("✓ Added dept_id FK to Course")
145 print(" - Each course belongs to one department")
146 print(" - Each department offers many courses\n")
147
148 # 1:N: Professor teaches many Courses
149 print("Relationship: Professor -[Teaches]-> Course (1:N)")
150 cursor.execute('ALTER TABLE Course ADD COLUMN prof_id INTEGER')
151 cursor.execute('''
152 CREATE INDEX idx_course_prof ON Course(prof_id)
153 ''')
154 print("✓ Added prof_id FK to Course")
155 print(" - Each course is taught by one professor")
156 print(" - Each professor teaches many courses\n")
157
158 print("=" * 60)
159 print("STEP 5: M:N RELATIONSHIPS → SEPARATE RELATION")
160 print("=" * 60)
161 print()
162
163 # M:N: Students enroll in many Courses, Courses have many Students
164 print("Relationship: Student -[Enrolls]-> Course (M:N)")
165 cursor.execute('''
166 CREATE TABLE Enrolls (
167 student_id INTEGER,
168 course_id TEXT,
169 semester TEXT,
170 grade TEXT,
171 PRIMARY KEY (student_id, course_id, semester),
172 FOREIGN KEY (student_id) REFERENCES Student(student_id),
173 FOREIGN KEY (course_id) REFERENCES Course(course_id)
174 )
175 ''')
176 print("✓ Created table Enrolls (relationship table)")
177 print(" - PK: (student_id, course_id, semester)")
178 print(" - Attributes: grade (relationship attribute)\n")
179
180 # M:N: Professor advises many Students
181 print("Relationship: Professor -[Advises]-> Student (M:N)")
182 cursor.execute('''
183 CREATE TABLE Advises (
184 prof_id INTEGER,
185 student_id INTEGER,
186 start_date DATE,
187 PRIMARY KEY (prof_id, student_id),
188 FOREIGN KEY (prof_id) REFERENCES Professor(prof_id),
189 FOREIGN KEY (student_id) REFERENCES Student(student_id)
190 )
191 ''')
192 print("✓ Created table Advises (relationship table)")
193 print(" - PK: (prof_id, student_id)")
194 print(" - Attributes: start_date\n")
195
196 print("=" * 60)
197 print("STEP 6: MULTIVALUED ATTRIBUTES → SEPARATE RELATION")
198 print("=" * 60)
199 print()
200
201 # Multivalued attribute: Student.phone_numbers
202 print("Multivalued Attribute: Student.phone_numbers")
203 cursor.execute('''
204 CREATE TABLE StudentPhone (
205 student_id INTEGER,
206 phone_number TEXT,
207 PRIMARY KEY (student_id, phone_number),
208 FOREIGN KEY (student_id) REFERENCES Student(student_id)
209 ON DELETE CASCADE
210 )
211 ''')
212 print("✓ Created table StudentPhone")
213 print(" - PK: (student_id, phone_number)")
214 print(" - One row per phone number\n")
215
216 conn.commit()
217
218
219def insert_sample_data(conn: sqlite3.Connection):
220 """Insert sample data to demonstrate the mapping."""
221 cursor = conn.cursor()
222
223 print("=" * 60)
224 print("INSERTING SAMPLE DATA")
225 print("=" * 60)
226 print()
227
228 # Departments
229 cursor.execute("INSERT INTO Department (dept_id, name, building) VALUES (1, 'Computer Science', 'Gates')")
230 cursor.execute("INSERT INTO Department (dept_id, name, building) VALUES (2, 'Mathematics', 'Eckhart')")
231
232 # Professors
233 cursor.execute("INSERT INTO Professor VALUES (101, 'Dr. Smith', 95000, 1)")
234 cursor.execute("INSERT INTO Professor VALUES (102, 'Dr. Johnson', 90000, 1)")
235 cursor.execute("INSERT INTO Professor VALUES (103, 'Dr. Williams', 85000, 2)")
236
237 # Set department chairs (1:1 relationship)
238 cursor.execute("UPDATE Department SET chair_id = 101 WHERE dept_id = 1")
239 cursor.execute("UPDATE Department SET chair_id = 103 WHERE dept_id = 2")
240
241 # Students
242 cursor.execute("INSERT INTO Student VALUES (1001, 'Alice Brown', '2002-05-15', 3.8)")
243 cursor.execute("INSERT INTO Student VALUES (1002, 'Bob Davis', '2001-09-20', 3.5)")
244 cursor.execute("INSERT INTO Student VALUES (1003, 'Charlie Wilson', '2003-01-10', 3.9)")
245
246 # Dependents (weak entity)
247 cursor.execute("INSERT INTO Dependent VALUES (1001, 'Emma Brown', 'spouse')")
248 cursor.execute("INSERT INTO Dependent VALUES (1002, 'Lily Davis', 'child')")
249
250 # Student phones (multivalued attribute)
251 cursor.execute("INSERT INTO StudentPhone VALUES (1001, '555-1234')")
252 cursor.execute("INSERT INTO StudentPhone VALUES (1001, '555-5678')")
253 cursor.execute("INSERT INTO StudentPhone VALUES (1002, '555-9999')")
254
255 # Courses
256 cursor.execute("INSERT INTO Course VALUES ('CS101', 'Intro to CS', 3, 1, 101)")
257 cursor.execute("INSERT INTO Course VALUES ('CS201', 'Data Structures', 4, 1, 102)")
258 cursor.execute("INSERT INTO Course VALUES ('MATH101', 'Calculus I', 4, 2, 103)")
259
260 # Enrollments (M:N relationship)
261 cursor.execute("INSERT INTO Enrolls VALUES (1001, 'CS101', 'Fall2023', 'A')")
262 cursor.execute("INSERT INTO Enrolls VALUES (1001, 'MATH101', 'Fall2023', 'B')")
263 cursor.execute("INSERT INTO Enrolls VALUES (1002, 'CS101', 'Fall2023', 'B')")
264 cursor.execute("INSERT INTO Enrolls VALUES (1003, 'CS201', 'Spring2024', 'A')")
265
266 # Advising (M:N relationship)
267 cursor.execute("INSERT INTO Advises VALUES (101, 1001, '2023-09-01')")
268 cursor.execute("INSERT INTO Advises VALUES (101, 1003, '2023-09-01')")
269 cursor.execute("INSERT INTO Advises VALUES (102, 1002, '2023-09-01')")
270
271 conn.commit()
272 print("✓ Sample data inserted\n")
273
274
275def demonstrate_queries(conn: sqlite3.Connection):
276 """Demonstrate queries on the mapped relational schema."""
277 cursor = conn.cursor()
278
279 print("=" * 60)
280 print("QUERY EXAMPLES")
281 print("=" * 60)
282 print()
283
284 # Query 1: Show all relationships
285 print("1. Students with their advisors (M:N relationship)")
286 print("-" * 60)
287 cursor.execute("""
288 SELECT S.name, P.name, A.start_date
289 FROM Student S
290 JOIN Advises A ON S.student_id = A.student_id
291 JOIN Professor P ON A.prof_id = P.prof_id
292 """)
293 for row in cursor.fetchall():
294 print(f" {row[0]} advised by {row[1]} since {row[2]}")
295
296 # Query 2: Weak entity access
297 print("\n2. Students and their dependents (weak entity)")
298 print("-" * 60)
299 cursor.execute("""
300 SELECT S.name, D.dependent_name, D.relationship
301 FROM Student S
302 JOIN Dependent D ON S.student_id = D.student_id
303 """)
304 for row in cursor.fetchall():
305 print(f" {row[0]}'s {row[2]}: {row[1]}")
306
307 # Query 3: Multivalued attribute
308 print("\n3. Students with multiple phone numbers (multivalued attribute)")
309 print("-" * 60)
310 cursor.execute("""
311 SELECT S.name, GROUP_CONCAT(SP.phone_number, ', ') as phones
312 FROM Student S
313 JOIN StudentPhone SP ON S.student_id = SP.student_id
314 GROUP BY S.student_id, S.name
315 """)
316 for row in cursor.fetchall():
317 print(f" {row[0]}: {row[1]}")
318
319 # Query 4: 1:1 relationship
320 print("\n4. Department chairs (1:1 relationship)")
321 print("-" * 60)
322 cursor.execute("""
323 SELECT D.name as dept, P.name as chair
324 FROM Department D
325 LEFT JOIN Professor P ON D.chair_id = P.prof_id
326 """)
327 for row in cursor.fetchall():
328 print(f" {row[0]} chaired by {row[1]}")
329
330 # Query 5: 1:N relationships
331 print("\n5. Courses with department and professor (1:N relationships)")
332 print("-" * 60)
333 cursor.execute("""
334 SELECT C.course_id, C.title, D.name as dept, P.name as prof
335 FROM Course C
336 JOIN Department D ON C.dept_id = D.dept_id
337 JOIN Professor P ON C.prof_id = P.prof_id
338 """)
339 for row in cursor.fetchall():
340 print(f" {row[0]} ({row[1]})")
341 print(f" Offered by: {row[2]}")
342 print(f" Taught by: {row[3]}")
343
344 # Query 6: M:N with attributes
345 print("\n6. Student enrollments with grades (M:N with attributes)")
346 print("-" * 60)
347 cursor.execute("""
348 SELECT S.name, C.course_id, E.semester, E.grade
349 FROM Student S
350 JOIN Enrolls E ON S.student_id = E.student_id
351 JOIN Course C ON E.course_id = C.course_id
352 ORDER BY S.name
353 """)
354 for row in cursor.fetchall():
355 print(f" {row[0]}: {row[1]} ({row[2]}) - Grade: {row[3]}")
356
357
358def demonstrate_cascade_delete(conn: sqlite3.Connection):
359 """Demonstrate cascading delete for weak entities."""
360 print("\n" + "=" * 60)
361 print("WEAK ENTITY CASCADE DELETE")
362 print("=" * 60)
363 print()
364
365 cursor = conn.cursor()
366
367 # Show dependent before delete
368 cursor.execute("SELECT * FROM Dependent WHERE student_id = 1001")
369 print("Before deleting student 1001:")
370 for row in cursor.fetchall():
371 print(f" Dependent: {row}")
372
373 # Delete student (should cascade to dependent)
374 cursor.execute("DELETE FROM Student WHERE student_id = 1001")
375
376 cursor.execute("SELECT * FROM Dependent WHERE student_id = 1001")
377 result = cursor.fetchall()
378 print("\nAfter deleting student 1001:")
379 if result:
380 for row in result:
381 print(f" Dependent: {row}")
382 else:
383 print(" ✓ Dependent also deleted (CASCADE)")
384
385 conn.commit()
386
387
388if __name__ == "__main__":
389 print("""
390╔══════════════════════════════════════════════════════════════╗
391║ ER MODEL TO RELATIONAL SCHEMA MAPPING ║
392║ 7-Step Algorithm: Entities → Relations ║
393╚══════════════════════════════════════════════════════════════╝
394""")
395
396 conn = sqlite3.connect(':memory:')
397 conn.execute('PRAGMA foreign_keys = ON') # Enable FK constraints
398
399 try:
400 create_university_er_model(conn)
401 insert_sample_data(conn)
402 demonstrate_queries(conn)
403 demonstrate_cascade_delete(conn)
404
405 print("\n" + "=" * 60)
406 print("SUMMARY OF ER-TO-RELATIONAL MAPPING")
407 print("=" * 60)
408 print("1. Regular entity → Table with PK")
409 print("2. Weak entity → Table with owner's PK + partial key")
410 print("3. 1:1 relationship → FK in either table (UNIQUE)")
411 print("4. 1:N relationship → FK on N side")
412 print("5. M:N relationship → Separate table with both PKs")
413 print("6. Multivalued attr → Separate table")
414 print("7. N-ary relation → Table with all entity PKs")
415 print("=" * 60)
416
417 finally:
418 conn.close()