Study the books.sql script provided in the ch17 examples folder's sql subfolder. Save the script as addressbook.sql and modify it to create a single table named contacts. The table should contain an auto-incremented id column and text columns for a person's first name, last name and phone number. make a Python script demonstrating: 1. insert contacts into the database, 2. query the database to list all the contacts and contacts with specific last name, 3. update a contact and 4. delete a contact. books.sql code: import panda as pd import sqlite3 connection = sqlite3.connect('books.db') pd.options.display.max_columns = 10 pd.read_sql("SELECT * FROM  authors",connection)  df = pd.read_sql( "SELECT * FROM  authors",connection) DROP TABLE IF EXISTS author_ISBN; DROP TABLE IF EXISTS titles; DROP TABLE IF EXISTS authors; CREATE TABLE authors (     id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,     first TEXT NOT NULL,     last TEXT NOT NULL ); CREATE TABLE titles (     isbn TEXT NOT NULL,     title TEXT NOT NULL,     edition INTEGER NOT NULL,     copyright TEXT NOT NULL,     PRIMARY KEY (isbn) ); CREATE TABLE author_ISBN (     id INTEGER NOT NULL,     isbn TEXT NOT NULL,     PRIMARY KEY (id, isbn),     FOREIGN KEY (id) REFERENCES authors(id) ON DELETE CASCADE,      FOREIGN KEY (isbn) REFERENCES titles (isbn) ON DELETE CASCADE ); PRAGMA foreign_keys = ON; INSERT INTO authors (first, last) VALUES      ('Paul','Deitel'),      ('Harvey','Deitel'),     ('Abbey','Deitel'),      ('Dan','Quirk'),     ('Alexander', 'Wald'); INSERT INTO titles (isbn,title,edition,copyright) VALUES     ('0135404673','Intro to Python for CS and DS',1,'2020'),     ('0132151006','Internet & WWW How to Program',5,'2012'),     ('0134743350','Java How to Program',11,'2018'),     ('0133976890','C How to Program',8,'2016'),      ('0133406954','Visual Basic 2012 How to Program',6,'2014'),     ('0134601548','Visual C# How to Program',6,'2017'),     ('0136151574','Visual C++ How to Program',2,'2008'),     ('0134448235','C++ How to Program',10,'2017'),     ('0134444302','Android How to Program',3,'2017'),     ('0134289366','Android 6 for Programmers',3,'2016'); INSERT INTO author_ISBN (id,isbn) VALUES     (1,'0134289366'),     (2,'0134289366'),     (5,'0134289366'),     (1,'0135404673'),     (2,'0135404673'),     (1,'0132151006'),     (2,'0132151006'),     (3,'0132151006'),     (1,'0134743350'),     (2,'0134743350'),     (1,'0133976890'),     (2,'0133976890'),     (1,'0133406954'),     (2,'0133406954'),     (3,'0133406954'),     (1,'0134601548'),     (2,'0134601548'),     (1,'0136151574'),     (2,'0136151574'),     (4,'0136151574'),     (1,'0134448235'),     (2,'0134448235'),     (1,'0134444302'),     (2,'0134444302'

Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
icon
Related questions
Question

7.3 (Contacts Database) Study the books.sql script provided in the ch17 examples folder's sql subfolder. Save the script as addressbook.sql and modify it to create a single table named contacts. The table should contain an auto-incremented id column and text columns for a person's first name, last name and phone number. make a Python script demonstrating: 1. insert contacts into the database, 2. query the database to list all the contacts and contacts with specific last name, 3. update a contact and 4. delete a contact.

books.sql code:

import panda as pd
import sqlite3
connection = sqlite3.connect('books.db')
pd.options.display.max_columns = 10
pd.read_sql("SELECT * FROM  authors",connection)
 df = pd.read_sql( "SELECT * FROM  authors",connection)


DROP TABLE IF EXISTS author_ISBN;
DROP TABLE IF EXISTS titles;
DROP TABLE IF EXISTS authors;

CREATE TABLE authors (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    first TEXT NOT NULL,
    last TEXT NOT NULL
);

CREATE TABLE titles (
    isbn TEXT NOT NULL,
    title TEXT NOT NULL,
    edition INTEGER NOT NULL,
    copyright TEXT NOT NULL,
    PRIMARY KEY (isbn)
);

CREATE TABLE author_ISBN (
    id INTEGER NOT NULL,
    isbn TEXT NOT NULL,
    PRIMARY KEY (id, isbn),
    FOREIGN KEY (id) REFERENCES authors(id) ON DELETE CASCADE, 
    FOREIGN KEY (isbn) REFERENCES titles (isbn) ON DELETE CASCADE
);

PRAGMA foreign_keys = ON;

INSERT INTO authors (first, last)
VALUES 
    ('Paul','Deitel'), 
    ('Harvey','Deitel'),
    ('Abbey','Deitel'), 
    ('Dan','Quirk'),
    ('Alexander', 'Wald');

INSERT INTO titles (isbn,title,edition,copyright)
VALUES
    ('0135404673','Intro to Python for CS and DS',1,'2020'),
    ('0132151006','Internet & WWW How to Program',5,'2012'),
    ('0134743350','Java How to Program',11,'2018'),
    ('0133976890','C How to Program',8,'2016'), 
    ('0133406954','Visual Basic 2012 How to Program',6,'2014'),
    ('0134601548','Visual C# How to Program',6,'2017'),
    ('0136151574','Visual C++ How to Program',2,'2008'),
    ('0134448235','C++ How to Program',10,'2017'),
    ('0134444302','Android How to Program',3,'2017'),
    ('0134289366','Android 6 for Programmers',3,'2016');

INSERT INTO author_ISBN (id,isbn)
VALUES
    (1,'0134289366'),
    (2,'0134289366'),
    (5,'0134289366'),
    (1,'0135404673'),
    (2,'0135404673'),
    (1,'0132151006'),
    (2,'0132151006'),
    (3,'0132151006'),
    (1,'0134743350'),
    (2,'0134743350'),
    (1,'0133976890'),
    (2,'0133976890'),
    (1,'0133406954'),
    (2,'0133406954'),
    (3,'0133406954'),
    (1,'0134601548'),
    (2,'0134601548'),
    (1,'0136151574'),
    (2,'0136151574'),
    (4,'0136151574'),
    (1,'0134448235'),
    (2,'0134448235'),
    (1,'0134444302'),
    (2,'0134444302'

Expert Solution
steps

Step by step

Solved in 5 steps with 4 images

Blurred answer
Knowledge Booster
Procedures in SQL
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education