Index: .idea/sqldialects.xml
===================================================================
--- .idea/sqldialects.xml (revision a2410a982f33cc4a7d72a3d3e6658141c2a32cc9)
+++ .idea/sqldialects.xml (revision 5e686ce10af40d42ea2bf750547dab2436169241)
@@ -5,4 +5,5 @@
+
Index: MuiscOrganizationSystem/settings.py
===================================================================
--- MuiscOrganizationSystem/settings.py (revision a2410a982f33cc4a7d72a3d3e6658141c2a32cc9)
+++ MuiscOrganizationSystem/settings.py (revision 5e686ce10af40d42ea2bf750547dab2436169241)
@@ -75,4 +75,23 @@
# REMOTE DM
+# DATABASES = {
+# "default": {
+# # "ENGINE": "django.db.backends.sqlite3",
+# # "NAME": BASE_DIR / "db.sqlite3",
+# 'ENGINE': 'django.db.backends.postgresql',
+#
+# 'NAME': 'db_202425z_va_prj_mpms',
+#
+# 'USER': 'db_202425z_va_prj_mpms_owner',
+#
+# 'PASSWORD': 'a225db474891',
+#
+# 'HOST': 'localhost',
+#
+# 'PORT': '7777',
+# }
+# }
+
+# LOCAL DB
DATABASES = {
"default": {
@@ -81,34 +100,15 @@
'ENGINE': 'django.db.backends.postgresql',
- 'NAME': 'db_202425z_va_prj_mpms',
+ 'NAME': 'chinook',
- 'USER': 'db_202425z_va_prj_mpms_owner',
+ 'USER': 'postgres',
- 'PASSWORD': 'a225db474891',
+ 'PASSWORD': 'postgres',
'HOST': 'localhost',
- 'PORT': '7777',
+ 'PORT': '5432',
}
}
-
-# LOCAL DB
-# DATABASES = {
-# "default": {
-# # "ENGINE": "django.db.backends.sqlite3",
-# # "NAME": BASE_DIR / "db.sqlite3",
-# 'ENGINE': 'django.db.backends.postgresql',
-#
-# 'NAME': 'chinook',
-#
-# 'USER': 'postgres',
-#
-# 'PASSWORD': 'postgres',
-#
-# 'HOST': 'localhost',
-#
-# 'PORT': '5432',
-# }
-# }
# Password validation
Index: SQL/DDL/primary_ddl.sql
===================================================================
--- SQL/DDL/primary_ddl.sql (revision a2410a982f33cc4a7d72a3d3e6658141c2a32cc9)
+++ SQL/DDL/primary_ddl.sql (revision 5e686ce10af40d42ea2bf750547dab2436169241)
@@ -136,2 +136,82 @@
CONSTRAINT address_info_pkey PRIMARY KEY (address_info_id)
);
+
+ALTER TABLE album
+ ADD CONSTRAINT fk_album_artist FOREIGN KEY (artist_id)
+ REFERENCES artist(artist_id)
+ ON DELETE CASCADE;
+
+ALTER TABLE customer
+ ADD CONSTRAINT fk_customer_employee FOREIGN KEY (support_rep_id)
+ REFERENCES employee(employee_id)
+ ON DELETE SET NULL;
+
+ALTER TABLE customer
+ ADD CONSTRAINT fk_customer_contact FOREIGN KEY (contact_id)
+ REFERENCES contact(contact_id)
+ ON DELETE SET NULL;
+
+ALTER TABLE customer
+ ADD CONSTRAINT fk_customer_address FOREIGN KEY (address_info_id)
+ REFERENCES addressinfo(address_info_id)
+ ON DELETE SET NULL;
+
+ALTER TABLE employee
+ ADD CONSTRAINT fk_employee_manager FOREIGN KEY (reports_to)
+ REFERENCES employee(employee_id)
+ ON DELETE SET NULL;
+
+ALTER TABLE employee
+ ADD CONSTRAINT fk_employee_contact FOREIGN KEY (contact_id)
+ REFERENCES contact(contact_id)
+ ON DELETE SET NULL;
+
+ALTER TABLE employee
+ ADD CONSTRAINT fk_employee_address FOREIGN KEY (address_info_id)
+ REFERENCES addressinfo(address_info_id)
+ ON DELETE SET NULL;
+
+ALTER TABLE invoice
+ ADD CONSTRAINT fk_invoice_customer FOREIGN KEY (customer_id)
+ REFERENCES customer(customer_id)
+ ON DELETE CASCADE;
+
+ALTER TABLE invoice_line
+ ADD CONSTRAINT fk_invoice_line_invoice FOREIGN KEY (invoice_id)
+ REFERENCES invoice(invoice_id)
+ ON DELETE CASCADE;
+
+ALTER TABLE invoice_line
+ ADD CONSTRAINT fk_invoice_line_track FOREIGN KEY (track_id)
+ REFERENCES track(track_id)
+ ON DELETE CASCADE;
+
+ALTER TABLE track
+ ADD CONSTRAINT fk_track_album FOREIGN KEY (album_id)
+ REFERENCES album(album_id)
+ ON DELETE SET NULL;
+
+ALTER TABLE track
+ ADD CONSTRAINT fk_track_media FOREIGN KEY (media_type_id)
+ REFERENCES media_type(media_type_id)
+ ON DELETE RESTRICT;
+
+ALTER TABLE track
+ ADD CONSTRAINT fk_track_genre FOREIGN KEY (genre_id)
+ REFERENCES genre(genre_id)
+ ON DELETE SET NULL;
+
+ALTER TABLE playlist_track
+ ADD CONSTRAINT fk_playlist_track_playlist FOREIGN KEY (playlist_id)
+ REFERENCES playlist(playlist_id)
+ ON DELETE CASCADE;
+
+ALTER TABLE playlist_track
+ ADD CONSTRAINT fk_playlist_track_track FOREIGN KEY (track_id)
+ REFERENCES track(track_id)
+ ON DELETE CASCADE;
+
+ALTER TABLE price
+ ADD CONSTRAINT fk_price_track FOREIGN KEY (track_id)
+ REFERENCES track(track_id)
+ ON DELETE CASCADE;
Index: explain_plan.txt
===================================================================
--- explain_plan.txt (revision 5e686ce10af40d42ea2bf750547dab2436169241)
+++ explain_plan.txt (revision 5e686ce10af40d42ea2bf750547dab2436169241)
@@ -0,0 +1,135 @@
+#WITH OUT INDICES
+
+Hash Join (cost=104.63..105.59 rows=1 width=524) (actual time=0.254..0.258 rows=8 loops=1)
+ Hash Cond: ((pc.genre_id = playcounts.genre_id) AND (pc.play_count = (max(playcounts.play_count))))
+ Buffers: shared hit=223
+ CTE playcounts
+ -> HashAggregate (cost=102.35..102.73 rows=38 width=291) (actual time=0.234..0.238 rows=15 loops=1)
+" Group Key: g.genre_id, ar.name"
+ Batches: 1 Memory Usage: 24kB
+ Buffers: shared hit=223
+ -> Hash Join (cost=91.39..102.07 rows=38 width=283) (actual time=0.189..0.224 rows=38 loops=1)
+ Hash Cond: (tr.genre_id = g.genre_id)
+ Buffers: shared hit=223
+ -> Nested Loop (cost=89.82..100.38 rows=38 width=25) (actual time=0.162..0.192 rows=38 loops=1)
+ Buffers: shared hit=222
+ -> Seq Scan on customer c (cost=0.00..3.67 rows=1 width=4) (actual time=0.008..0.012 rows=1 loops=1)
+ Filter: (customer_id = 5)
+ Rows Removed by Filter: 53
+ Buffers: shared hit=3
+ -> Hash Join (cost=89.82..96.33 rows=38 width=29) (actual time=0.153..0.177 rows=38 loops=1)
+ Hash Cond: (ar.artist_id = a.artist_id)
+ Buffers: shared hit=219
+ -> Seq Scan on artist ar (cost=0.00..4.75 rows=275 width=25) (actual time=0.005..0.015 rows=275 loops=1)
+ Buffers: shared hit=2
+ -> Hash (cost=89.35..89.35 rows=38 width=12) (actual time=0.138..0.138 rows=38 loops=1)
+ Buckets: 1024 Batches: 1 Memory Usage: 10kB
+ Buffers: shared hit=217
+ -> Nested Loop (cost=4.91..89.35 rows=38 width=12) (actual time=0.033..0.133 rows=38 loops=1)
+ Buffers: shared hit=217
+ -> Nested Loop (cost=4.76..82.86 rows=38 width=12) (actual time=0.028..0.099 rows=38 loops=1)
+ Buffers: shared hit=141
+ -> Nested Loop (cost=4.48..67.66 rows=38 width=8) (actual time=0.022..0.047 rows=38 loops=1)
+ Buffers: shared hit=27
+ -> Bitmap Heap Scan on invoice i (cost=4.20..10.60 rows=7 width=8) (actual time=0.015..0.018 rows=7 loops=1)
+ Recheck Cond: (customer_id = 5)
+ Heap Blocks: exact=5
+ Buffers: shared hit=6
+ -> Bitmap Index Scan on invoice_customer_id_idx (cost=0.00..4.20 rows=7 width=0) (actual time=0.009..0.009 rows=7 loops=1)
+ Index Cond: (customer_id = 5)
+ Buffers: shared hit=1
+ -> Index Scan using invoice_line_invoice_id_idx on invoice_line il (cost=0.28..8.10 rows=5 width=8) (actual time=0.002..0.003 rows=5 loops=7)
+ Index Cond: (invoice_id = i.invoice_id)
+ Buffers: shared hit=21
+ -> Index Scan using track_pkey on track tr (cost=0.28..0.40 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=38)
+ Index Cond: (track_id = il.track_id)
+ Buffers: shared hit=114
+ -> Index Scan using album_pkey on album a (cost=0.15..0.17 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=38)
+ Index Cond: (album_id = tr.album_id)
+ Buffers: shared hit=76
+ -> Hash (cost=1.25..1.25 rows=25 width=262) (actual time=0.023..0.023 rows=25 loops=1)
+ Buckets: 1024 Batches: 1 Memory Usage: 10kB
+ Buffers: shared hit=1
+ -> Seq Scan on genre g (cost=0.00..1.25 rows=25 width=262) (actual time=0.013..0.015 rows=25 loops=1)
+ Buffers: shared hit=1
+ -> CTE Scan on playcounts pc (cost=0.00..0.76 rows=38 width=528) (actual time=0.236..0.237 rows=15 loops=1)
+ Buffers: shared hit=223
+ -> Hash (cost=1.33..1.33 rows=38 width=12) (actual time=0.012..0.012 rows=8 loops=1)
+ Buckets: 1024 Batches: 1 Memory Usage: 9kB
+ -> HashAggregate (cost=0.95..1.33 rows=38 width=12) (actual time=0.009..0.010 rows=8 loops=1)
+ Group Key: playcounts.genre_id
+ Batches: 1 Memory Usage: 24kB
+ -> CTE Scan on playcounts (cost=0.00..0.76 rows=38 width=12) (actual time=0.001..0.006 rows=15 loops=1)
+Planning:
+ Buffers: shared hit=110 dirtied=1
+Planning Time: 3.223 ms
+Execution Time: 0.372 ms
+
+
+#WITH INDICES
+
+Hash Join (cost=100.63..101.59 rows=1 width=524) (actual time=0.249..0.254 rows=8 loops=1)
+ Hash Cond: ((pc.genre_id = playcounts.genre_id) AND (pc.play_count = (max(playcounts.play_count))))
+ Buffers: shared hit=223
+ CTE playcounts
+ -> HashAggregate (cost=98.35..98.73 rows=38 width=291) (actual time=0.230..0.233 rows=15 loops=1)
+" Group Key: g.genre_id, ar.name"
+ Batches: 1 Memory Usage: 24kB
+ Buffers: shared hit=223
+ -> Hash Join (cost=87.39..98.07 rows=38 width=283) (actual time=0.185..0.220 rows=38 loops=1)
+ Hash Cond: (tr.genre_id = g.genre_id)
+ Buffers: shared hit=223
+ -> Nested Loop (cost=85.82..96.38 rows=38 width=25) (actual time=0.157..0.186 rows=38 loops=1)
+ Buffers: shared hit=222
+ -> Seq Scan on customer c (cost=0.00..3.67 rows=1 width=4) (actual time=0.008..0.011 rows=1 loops=1)
+ Filter: (customer_id = 5)
+ Rows Removed by Filter: 53
+ Buffers: shared hit=3
+ -> Hash Join (cost=85.82..92.33 rows=38 width=29) (actual time=0.148..0.172 rows=38 loops=1)
+ Hash Cond: (ar.artist_id = a.artist_id)
+ Buffers: shared hit=219
+ -> Seq Scan on artist ar (cost=0.00..4.75 rows=275 width=25) (actual time=0.006..0.016 rows=275 loops=1)
+ Buffers: shared hit=2
+ -> Hash (cost=85.35..85.35 rows=38 width=12) (actual time=0.133..0.133 rows=38 loops=1)
+ Buckets: 1024 Batches: 1 Memory Usage: 10kB
+ Buffers: shared hit=217
+ -> Nested Loop (cost=4.91..85.35 rows=38 width=12) (actual time=0.031..0.127 rows=38 loops=1)
+ Buffers: shared hit=217
+ -> Nested Loop (cost=4.76..78.86 rows=38 width=12) (actual time=0.027..0.096 rows=38 loops=1)
+ Buffers: shared hit=141
+ -> Nested Loop (cost=4.48..63.66 rows=38 width=8) (actual time=0.022..0.046 rows=38 loops=1)
+ Buffers: shared hit=27
+ -> Bitmap Heap Scan on invoice i (cost=4.20..10.60 rows=7 width=8) (actual time=0.015..0.019 rows=7 loops=1)
+ Recheck Cond: (customer_id = 5)
+ Heap Blocks: exact=5
+ Buffers: shared hit=6
+ -> Bitmap Index Scan on idx_invoice_customer_id (cost=0.00..4.20 rows=7 width=0) (actual time=0.008..0.008 rows=7 loops=1)
+ Index Cond: (customer_id = 5)
+ Buffers: shared hit=1
+ -> Index Scan using idx_invoice_line_invoice_id on invoice_line il (cost=0.28..7.53 rows=5 width=8) (actual time=0.002..0.003 rows=5 loops=7)
+ Index Cond: (invoice_id = i.invoice_id)
+ Buffers: shared hit=21
+ -> Index Scan using track_pkey on track tr (cost=0.28..0.40 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=38)
+ Index Cond: (track_id = il.track_id)
+ Buffers: shared hit=114
+ -> Index Scan using album_pkey on album a (cost=0.15..0.17 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=38)
+ Index Cond: (album_id = tr.album_id)
+ Buffers: shared hit=76
+ -> Hash (cost=1.25..1.25 rows=25 width=262) (actual time=0.024..0.024 rows=25 loops=1)
+ Buckets: 1024 Batches: 1 Memory Usage: 10kB
+ Buffers: shared hit=1
+ -> Seq Scan on genre g (cost=0.00..1.25 rows=25 width=262) (actual time=0.015..0.017 rows=25 loops=1)
+ Buffers: shared hit=1
+ -> CTE Scan on playcounts pc (cost=0.00..0.76 rows=38 width=528) (actual time=0.231..0.232 rows=15 loops=1)
+ Buffers: shared hit=223
+ -> Hash (cost=1.33..1.33 rows=38 width=12) (actual time=0.013..0.013 rows=8 loops=1)
+ Buckets: 1024 Batches: 1 Memory Usage: 9kB
+ -> HashAggregate (cost=0.95..1.33 rows=38 width=12) (actual time=0.009..0.011 rows=8 loops=1)
+ Group Key: playcounts.genre_id
+ Batches: 1 Memory Usage: 24kB
+ -> CTE Scan on playcounts (cost=0.00..0.76 rows=38 width=12) (actual time=0.001..0.006 rows=15 loops=1)
+Planning:
+ Buffers: shared hit=51
+Planning Time: 0.858 ms
+Execution Time: 0.345 ms
+