--> Skip to main content

Featured

Steps to Create a Project on GitHub

Steps to create a project on GitHub:  1.   Start a repo on GitHub 2.   Make a README.md file 3.   Open vs code – new folder – new terminal – git clone http:…. (from the repo). 4.   In terminal – cd theprojectname   à move into the project file 5.   Ls -la is for showing hidden file à not working for me ???? 6.   Make some changes to README file, in terminal git status à shows all the changes on the file 7.   To add all changes update to the folder à git add . (the dot . means all changes to be added) 8.   Add a new file index.html, in terminal à git commit -m “title of commit” -m “description of commit” 9.   Then git push origin master 10.                 ****Initial a repo in local text editor à git init 11.                 After use git add . etc, when pus...
Read more
Labels

Python SQLite3 Module Database - Many to Many Relationship Database Example


The best example to understand many to many relationship is student and class. A class can have many students to enroll, a student can go to several classes, so class and student relationship is many to many relationship.
Many to many relationship creates data redundancy, which is discouraged in relational database design. A way to solve many to many relationship is to create a 'linking table' in the middle, it also called 'join table', so that a many to many relationship can be converted to two one-to-many relationships.
A linking table must contain at least two columns, each column corresponds to the primary key from one origianl table. A composite primary key is then formed by combining these two columns, that is the constraint for making sure each pair combination of primary keys from two original tables can only appear once in the linking table.

When a linking table is created, it sometimes makes more sense to move some non essential attributes out of the two tables and into the linking table. For this simple example, I don't have this practice. 

import sqlite3

# create a database with many to many relationship tables
# student, class and linking table enrollment, then join them

con = sqlite3.connect('school.sqlite')
cursor = con.cursor()

# create tables 
cursor.executescript('''
Drop Table if exists STUDENT;
Drop table if exists CLASS;
Drop table if exists ENROLLMENT;

Create Table STUDENT (
    id integer primary key autoincrement not null unique,
    name text,
    email text
);
Create Table CLASS (
    id integer primary key autoincrement not null unique,
    title text
);
Create Table ENROLLMENT (
    sid integer,
    cid integer,
    registerDate numeric,
    Primary key (sid, cid)
);
''')

# add data into tables
cursor.executescript('''
INSERT INTO STUDENT (name, email) values 
('peter low','pl@email.com'), 
('alice smith', 'alices@yahoo.com'),
('joseph robertson', 'jorobert@yahoo.com'),
('tee mathew', 'teema@mail.com');

INSERT INTO CLASS (title) values ('cs'), ('music'), ('biochemistry');

INSERT INTO ENROLLMENT (sid, cid, registerDate) values 
(1,1,20200311),
(1,3,20210122),
(2,1,20220203),
(3,1,20200910),
(3,3,20211010),
(2,2,20220202),
(4,2,20211002),
(4,3,20210920);
''')

# JOIN tables
cursor.execute('''
SELECT STUDENT.name, CLASS.title 
FROM STUDENT
JOIN ENROLLMENT
JOIN CLASS
ON STUDENT.id = ENROLLMENT.sid
AND class.id = ENROLLMENT.cid
ORDER BY STUDENT.name;

''')

for entry in cursor:
    print(entry)

# output:
# ('alice smith', 'cs')
# ('alice smith', 'music')
# ('joseph robertson', 'cs')
# ('joseph robertson', 'biochemistry')
# ('peter low', 'cs')
# ('peter low', 'biochemistry')
# ('tee mathew', 'music')
# ('tee mathew', 'biochemistry')


con.commit()
con.close()

To visualize the table created by joining the many to many relationship tables, execute the command on DB Browser. For source code, please visit github repo.
Labels:

Popular Posts