Pr9DBwG4

1'''
2Created on 2016 M03 14
3
4@author: ohxx5810
5'''
6import mysql.connector;
7
8def printMenu():
9    print("Menu:\n1. Search\n2. Register\n3. Add Items\n4. Check Out\n5. Claim Rewards\n");
10    option = input("Enter Selection: ");
11    return option;
12
13def selectOne():
14    print("Search: ");
15    
16    return;
17
18def selectTwo():
19    print("Selection 2 was chosen.");
20    
21    return;
22
23def selectThree():
24    print("Selection 3 was chosen.");
25    
26    return;
27
28def selectFour():
29    print("Selection 4 was chosen.");
30    
31    return;
32
33
34# Connect to database.
35db = mysql.connector.connect ( 
36  user = 'ohxx5810',
37  host ='hopper.wlu.ca',
38  password = 'bigtop4',
39  database = 'ohxx5810'
40  );
41
42cursor = db.cursor();
43
44# CREATE THE TABLES
45 
46# Table Musicians
47statement = """ CREATE TABLE IF NOT EXISTS Musicians
48                ( ssn CHAR(10),
49                  name CHAR(20),
50                  PRIMARY KEY (ssn))""";
51cursor.execute(statement);
52# Table Album_Producer
53statement = """ CREATE TABLE IF NOT EXISTS Album_Producer
54                ( albumIdentifier INTEGER NOT NULL AUTO_INCREMENT,
55                  ssn CHAR(10),
56                  copyrightDate DATE,
57                  title CHAR(30),
58                  PRIMARY KEY (albumIdentifier),
59                  FOREIGN KEY (ssn) REFERENCES Musicians(ssn) )""";
60cursor.execute(statement);
61# Table Songs_Appears
62statement = """ CREATE TABLE IF NOT EXISTS Songs_Appears
63                ( songId INTEGER NOT NULL AUTO_INCREMENT,
64                  author CHAR(30), title CHAR(30),
65                  albumIdentifier INTEGER NOT NULL,
66                  PRIMARY KEY (songId),
67                  FOREIGN KEY (albumIdentifier) REFERENCES Album_Producer(albumIdentifier))""";
68cursor.execute(statement);
69# Table Perform
70statement = """CREATE TABLE IF NOT EXISTS Perform
71               ( songId INTEGER,
72                 ssn VARCHAR(10),
73                 PRIMARY KEY (ssn, songId),
74                 FOREIGN KEY (songId) REFERENCES Songs_Appears(songId),
75                 FOREIGN KEY (ssn) REFERENCES Musicians(ssn) )""";
76cursor.execute(statement);
77# Table Registered Users
78statement = """ CREATE TABLE IF NOT EXISTS Users
79                ( uid INTEGER NOT NULL AUTO_INCREMENT,
80                  username VARCHAR(20),
81                  password VARCHAR(20),
82                  address VARCHAR(30),
83                  PRIMARY KEY (uid))""";
84cursor.execute(statement);
85# Table Credit Cards
86statement = """ CREATE TABLE IF NOT EXISTS CreditCards
87                (uid INTEGER,
88                 cardNumber INTEGER,
89                 PRIMARY KEY (cardNumber),
90                 FOREIGN KEY (uid) REFERENCES Users(uid))""";
91cursor.execute(statement);
92# Table Likes
93statement = """ CREATE TABLE IF NOT EXISTS Likes
94                ( lid INTEGER NOT NULL AUTO_INCREMENT,
95                  uid INTEGER NOT NULL,
96                  ssn VARCHAR(10),
97                  PRIMARY KEY (lid),
98                  FOREIGN KEY (uid) REFERENCES Users(uid),
99                  FOREIGN KEY (ssn) REFERENCES MUSICIANS(ssn) ) """;
100# Table Cart
101statement = """ CREATE TABLE IF NOT EXISTS Cart
102                 (cid INTEGER NOT NULL AUTO_INCREMENT,
103                  uid INTEGER,
104                  albumIdentifier INTEGER,
105                  PRIMARY KEY (cid),
106                  FOREIGN KEY (uid) REFERENCES Users(uid),
107                  FOREIGN KEY (albumIdentifier) REFERENCES Album_Producer(albumIdentifier))""";
108cursor.execute(statement);
109
110i=0;
111while i<1:
112    selection = printMenu();
113    if selection == '1':
114        selectOne();
115    elif selection == '2':
116        selectTwo();
117    elif selection == '3':
118        selectThree();
119    elif selection == '4':
120        selectFour();
121    else:
122        print("No option selected.\n");
123
124
125
126statement = 'commit';
127cursor.execute(statement);
128
129cursor.close();
130db.close();