IS240Project 3Create a sqlLite database that contains an employee table. The table should contain...

Question

IS240Project 3Create a sqlLite database that contains an employee table.  The table should contain the following fields: EmployeeID, FirstName, LastName, Salary, YearStarted, DOB, ArchivedThe name of the database should be “Employee.db”.Present the user with a menu containing these options:1. Add an employee to the database2. Archive an Employee3. List all the Employees4. Show all unarchived employees5. Show all new employees who started their job on/or after 1/1/20176. ExitWhen the user chooses which menu item they want, the program should show them what they asked to see.  In case of 1 and 2, the program should ask the user all the information it needs about the new employee or ask which employee should be archived and change the database accordingly.The menu should then re-appear, so that the user can pick another option – until the user decides to exit the program.Make sure you save your Python files(s) to the weekly module associated with this assignment and name your Python files as W12P3xx, where xx is the first initial of your first and last name.  If you are not sure how to submit these files to Blackboard, submit your question on our course Discussion Board Questions forum.Notes:1. Name of Database is “Employees.db”2. Name of table is “tblEmployee3. tblEmployee field names and format are:Field Description    SQLITE field name and formata. Employee ID -    EmployeeID is integer format. First Name -     FirstName is text formatc. Last Name -    LastName is text formatd. Salary -        Salary is floating real formate. Year started -    Year started is integer format (yyyy)f. Date of Birth -    DOB is date format yyyy-mm-ddg. Archived -        Archived is text format (value “Y” or “N”)4. When you add an employee do not woy about data validation but make sure you follow the field format characteristics i.e. integer is a number without decimals, real is a number with decimals, text is text, date is in the format of yyyy-mm-dd.5. When you archive an employee, you change the Archived column from “N” to “Y”.6. When you list all the employees, output should be formatted according to field characteristics i.e. Salary would be $1,234.56 and they should be aligned by column for example:1    Steven        Engorn        $1,234.56    2016     XXXXXXXXXX3    Clifford    Baskerville    $5,432.10    2017     XXXXXXXXXX4    John        Smith        $ XXXXXXXXXX    2017     XXXXXXXXXX7. When you list all archived employees, use the same formatting as note 6 but only for employees where their Archived field is = “Y”.8. When you list all unarchived employees, use the same formatting as note 6 but only for employees where their Archived field is = “N”.9. When you list all employees, who started their job on/or after 1/1/2017, use the same formatting as note 6 but only include those employees whose YearStarted is greater than or equal 2017.

Rohith · Accepted Answer

56162.py
import sqlite3
from sqlite3 import Error
sql_create_employee_table = """ CREATE TABLE IF NOT EXISTS tblEmployee (
                                        EmployeeID integer PRIMARY KEY,
                                        FirstName text NOT NULL,
                                        LastName text NOT NULL,
                                        Salary real NOT NULL,
                                        YearStarted real NOT NULL ,
                                        DOB  date NOT NULL,
                                        Archived  text NOT NULL
                                    ); """
def create_connection(db_file):
    """ create a database connection to a SQLite database """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        print("Connection successful")
    except Error as e:
        print(e)
    return conn
def create_table(conn, create_table_sql):
    """ create a table from the create_table_sql statement
    :param conn: Connection object
    :param create_table_sql: a CREATE TABLE statement
    :return:
    """
    try:
        c = conn.cursor()
        c.

IS240 Project 3 Create a sqlLite database that contains an employee table. The table should contain the following fields: EmployeeID, FirstName, LastName, Salary, YearStarted, DOB, Archived The name...

Solution

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment