CentOS postgresql database with python connectivity
Microsoft Word - DbPROJ_phase1_F20.docx Capstone Project: Set up Python program to manipulate database Phase #1 Section A – Create database Exercise #1: Create role to manage database Create a role with superuser privileges and name it cdstore. It is your choice whether you want to create a corresponding Linux user. Exercise #2: Create database Create a database (you can use the PostgreSQL command or SQL statement – your choice). Name the database: cdstore_networkid Example: cdstore_smit0001 Section B – Setup database connection Exercise #1: Install the Python database connector Use the following commands to install the Python database connector: yum install postgresql-devel yum install python36-devel yum install python36-psycopg2 Exercise #2: Setup the database connection Create a Python script file and name it cdstore_networkid.py. Import psycopg2 and sys. Establish connection to database: try: connection=psycopg2.connect(database='name', user='role') except psycopg2.DatabaseError: print("Error: Connection to database not established.") sys.exit(1) # early exit if DB not available else: print(“Database connection established”) Close the connection: connection.close() Exercise #3: Test the database connection Execute the script to test database connectivity. Section C – Set up a Python program with limited functionality In the first phase of the project we are writing a Python “skeleton” program that will contain database connectivity and a main program loop. The specific program functionality will be completed in the second phase of the project. When writing the program make sure to place all Python statements in the proper order: Imports Function definitions Database connection Main program skeleton Closing of database connection Exercise #1: Create a function that displays the menu Create a function that displays the menu that provides an exit option, an option to insert a purchase, and a few query options: More specifically the menu provides the following options: o Exit o Insert a purchase record o Display all albums - album name, genre only - sorted by name o Display all albums - album name, genre only - sorted by genre o Display all albums - album name, genre and artist name only - by an artist o Display all purchases of a customer: display customer first & last name, album name, artist name. The function name is: display_menu. The function has no parameters and no return value. Exercise #2: Create a function that takes user input. The function prompts the user for a menu selection. Implement some error checking (keep it simple). The function name is: get_menu_selection. The function has one function parameter: the prompt for the input function; it returns the menu selection. Exercise #3: Create the main program loop Create a main loop that allows a user to select a menu option until the user wants to quit. Display the menu and prompt for a menu selection. Based on the menu selection the program prints the corresponding action. Example: If the user selects to query all albums sorted by genre display: “Displaying all albums, sorted by genre”. Note: The loop may not have any exit type statements; it may not be recursive; it may not use flags; it may not use global variables unless explicitly stated (cursor is the only global variable allowed): we expect a proper read loop. Section D – Refine database design Exercise #1: Create logical ERD based on conceptual ERD Based on the conceptual ERD create a logical ERD: entities with attributes, data types and constraints. Entity o Identify entity. o Resolve composite attributes. o Identify the primary key. o Identify other constraints such as “non null”. Relationship o Identify Relationships & cardinality. o Resolve many-to-many relationships and identify foreign keys. Identify multivalued attributes, if any. Follow naming convention to set entity names and attribute names. Determine the data type for each attribute. Exercise #2: ERD submission format The format for the logical ERD has be submitted as a PDF: it can be written in MS Word or using an ERD diagramming tool. If in MS Word use the following format: entity name o attribute #1 name: data type, constraints o attribute #2 name: datatype, constraints o … Note: Underline primary key, italicize secondary keys. Section E – Setup and populate tables Exercise #1: Create tables Create the following files to create tables (use file naming convention below or use your own file naming convention): o Album: createAlbumTable.sql o Artist: createArtistTable.sql o Customer: createCustomerTable.sql o Purchase: createPurchaseTable.sql In each file write the SQL statement to create the corresponding table. Note: If your primary key is an integer, use the datatype serial instead of integer. Connect to your cdstore database and upload the files (in the proper order: tables that have foreign keys are created after the tables that contain the corresponding primary keys). Exercise #2: Insert records into tables Create the following files to insert records into the tables (use file naming convention below or use your own file naming convention): o Album: insertAlbum.sql o Artist: insertArtist.sql o Customer: insertCustomer.sql o Purchase: insertPurchase.sql In each file write the SQL statement to create the corresponding records, a minimum of 8 records for customers, artists, and purchases; a minimum of 12 records for albums, some by the same artist, and some of the same genre (example: 2 blues albums by different blues artists). Syntax: INSERT INTO table (attribute_list) VALUES (customer attribute values), (customer attribute values), (customer attribute values); Example: INSERT INTO customer (customer_firstname, customer_lastname, customer_phone, customer_email) VALUES (‘Aaron’, ‘Adams’, ‘6131223456’, ‘
[email protected]’), (‘Berta’, ‘Binochet’, ‘6131233567’, ‘
[email protected]’), (‘Chris’, ‘Cesario’, ‘6131234467’, ‘
[email protected]’); Syntax example for foreign keys: INSERT INTO purchase (customer_id, album_id, purchase_date) VALUES ((select id from customer where customer_lastname = 'Adams'), (select id from album where album_name = 'Lucille'), ‘2020-12-24’); Connect to your cdstore database and upload the files. Exercise #3: Test database setup To verify that the SQL files uploaded properly: List all tables in the database. Execute a few simple queries. Section F – Form queries Exercise #1: Create all SQL query commands/statements Create the following queries (record them): o Display all albums, sorted by name o Display all albums, sorted by genre o Display all albums – album name, genre and artist name only - by an artist o Display all purchases of a customer: display customer first & last name, album name, artist name. Test the queries you created to verify that you obtain the expected result. Visio-Conceptual_ERD_cdstore_F20.vsdx album website name artist releases customername purchases email artist name genre number of tracks phone date