Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

To Do This project will be broken down into sections that you’ll want to complete in the order presented. Each section builds on the previous work, so be sure of your work on one section before...

1 answer below »
To Do This project will be broken down into sections that you’ll want to complete in the order presented. Each section builds on the previous work, so be sure of your work on one section before proceeding to the next. The Deliverables Your submission will be 2 files. 1. A PDF or Word document with your logical model diagram and SQL results screenshots. 2. A .sql file containing your DDL and DML Note: in order for you to receive the points, I must be able to execute your code without errors. This includes being able to execute the scripts to connect to your database in the iSchool SQL Server (ist-s-students.syr.edu). The Logical Model (30 points) Use Draw.io to convert the ERD shown above into a logical model diagram. Pay particular attention to the business rules and other considerations shown below the ERD. For column naming and data types, use the following: Table: prj_transaction Column Name Data Type Info transaction_id int PK, identity, this is a surrogate key transaction_date datetime Required, default to the current date and time transaction_type_id int Required, foreign key referencing prj_type 4
Answered Same Day Dec 04, 2021

Solution

Deepti answered on Dec 05 2021
142 Votes
48506-SQLServe
DDL-DML-SQLServer.sql
*DDL Statements:*
CREATE TABLE prj_part (
    part_id        INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    part_name    VARCHAR(30) NOT NULL UNIQUE,
    part_cost    FLOAT NOT NULL
);
CREATE Table prj_transaction_type(
    transaction_type_id        INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    trans_type_text            VARCHAR(30) NOT NULL UNIQUE
);
CREATE TABLE prj_transaction(
    transaction_id         INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    transaction_date        DATETIME NOT NULL DEFAULT(GETDATE()),
    transaction_type_id        INT NOT NULL,
    FOREIGN KEY (transaction_type_id) REFERENCES prj_transaction_type(transaction_type_id)
);
CREATE TABLE prj_transaction_part(
    tp_transaction_part_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    tp_transaction_id    INT NOT NULL,
    tp_part_id            INT NOT NULL,
    tp_quantity            INT NOT NULL,
    CONSTRAINT [UQ_transaction_part] UNIQUE NONCLUSTERED
    (
        tp_transaction_id, tp_part_id
    ),
    FOREIGN KEY (tp_transaction_id) REFERENCES prj_transaction(transaction_id),
    FOREIGN KEY (tp_part_id) REFERENCES prj_part(part_id)
);
*DML statements:*
INSERT INTO prj_transaction_type (trans_type_text) VALUES
    ('Purchase')
    ,('Used');
INSERT INTO prj_part (part_name, part_cost) VALUES
    ('Cognition amplifier', 956)
    , ('Combat suit', 163)
    , ('Dark matter', 968)
    , ('Alien Containment Box', 552)
    , ('Dream inceptor', 909)
    , ('Freeze ray', 143)
    , ('Ionic Defibulizer', 905)
    , ('Love Antidote', 891)
    , ('Love Potion', 244)
    , ('Meeseeks Box', 832)
    , ('Microverse battery', 262)
    , ('Operation Phoenix', 287)
    , ('Particle Beam Wrist Watch', 412)
    , ('Portal gun', 370)
    , ('Shrink ray', 277)
    , ('Space cruiser', 959);
INSERT INTO prj_transaction (transaction_date, transaction_type_id) VALUES
    ('1/1/2018', 1)
    , ('1/1/2018', 2)
    , ('2/1/2018', 1)
    , ('2/1/2018', 2)
    , ('3/1/2018', 1)
    , ('3/1/2018', 2)
    , ('4/1/2018', 1)
    , ('4/1/2018', 2)
    , ('5/1/2018', 1)
    , ('5/1/2018', 2)
    , ('6/1/2018', 1)
    , ('6/1/2018', 2)
    , ('7/1/2018', 1)
    , ('7/1/2018', 2)
    , ('8/1/2018', 1)
    , ('8/1/2018', 2)
    , ('9/1/2018', 1)
    , ('9/1/2018', 2)
    , ('10/1/2018', 1)
    , ('10/1/2018', 2)
    , ('11/1/2018', 1)
    , ('11/1/2018', 2)
    , ('12/1/2018', 1)
    , ('12/1/2018', 2)
    , ('1/1/2019', 1)
    , ('1/1/2019', 2)
    , ('2/1/2019', 1)
    , ('2/1/2019', 2)
    , ('3/1/2019', 1)
    , ('3/1/2019', 2)
    , ('4/1/2019', 1)
    , ('4/1/2019', 2)
    , ('5/1/2019', 1)
    , ('5/1/2019', 2)
    , ('6/1/2019', 1)
    , ('6/1/2019', 2)
    , ('7/1/2019', 1)
    , ('7/1/2019', 2)
    , ('8/1/2019', 1)
    , ('8/1/2019', 2)
    , ('9/1/2019', 1)
    , ('9/1/2019', 2)
    , ('10/1/2019', 1)
    , ('10/1/2019', 2);
INSERT INTO prj_transaction_part (tp_transaction_id, tp_part_id, tp_quantity) VALUES
(1, 1, 10)
, (1, 2, 10)
, (1, 3, 10)
, (1, 4, 10)
, (1, 5, 10)
, (1, 6, 10)
, (1, 7, 10)
, (1, 8, 10)
, (1, 9, 10)
, (1, 10, 10)
, (1, 11, 10)
, (1, 12, 10)
, (1, 13, 10)
, (1, 14, 10)
, (1, 15, 10)
, (1, 16, 10)
, (2, 1, -2)
, (2, 2, -10)
, (2, 3, 0)
, (2, 4, -4)
, (2, 5, -1)
, (2, 6, -6)
, (2, 7, -5)
, (2, 8, -3)
, (2, 9, -1)
, (2, 10, -2)
, (2, 11, -7)
, (2, 12, -3)
, (2, 13, -8)
, (2, 14, -8)
, (2, 15, -3)
, (2, 16, -10)
, (3, 1, 5)
, (3, 2, 10)
, (3, 3, 6)
, (3, 4, 3)
, (3, 5, 3)
, (3, 6, 4)
, (3, 7, 5)
, (3, 8, 4)
, (3, 9, 8)
, (3, 10, 0)
, (3, 11, 0)
, (3, 12, 6)
, (3, 13, 0)
, (3, 14, 2)
, (3, 15, 7)
, (3, 16, 10)
, (4, 1, -8)
, (4, 2, 0)
, (4, 3, -13)
, (4, 4, -3)
, (4, 5, -6)
, (4, 6, -1)
, (4, 7, -5)
, (4, 8, -10)
, (4, 9, -4)
, (4, 10, -4)
, (4, 11, -1)
, (4, 12, -2)
, (4, 13, -2)
, (4, 14, 0)
, (4, 15, -13)
, (4, 16, -8)
, (5, 1, 0)
, (5, 2, 2)
, (5, 3, 3)
, (5, 4, 1)
, (5, 5, 4)
, (5, 6, 0)
, (5, 7, 0)
, (5, 8, 9)
, (5, 9, 7)
, (5, 10, 4)
, (5, 11, 0)
, (5, 12, 6)
, (5, 13, 10)
, (5, 14, 0)
, (5, 15, 9)
, (5, 16, 2)
, (6, 1, -3)
, (6, 2, -8)
, (6, 3, 0)
, (6, 4, -7)
, (6, 5, -3)
, (6, 6, -4)
, (6, 7, 0)
, (6, 8, -10)
, (6, 9, -18)
, (6, 10, -6)
, (6, 11, -2)
, (6, 12, -9)
, (6, 13, -10)
, (6, 14, -2)
, (6, 15, -3)
, (6, 16, -4)
, (7, 1, 1)
, (7, 2, 0)
, (7, 3, 3)
, (7, 4, 10)
, (7, 5, 3)
, (7, 6, 1)
, (7, 7, 3)
, (7, 8, 10)
, (7, 9, 0)
, (7, 10, 2)
, (7, 11, 10)
, (7, 12, 2)
, (7, 13, 10)
, (7, 14, 1)
, (7, 15, 1)
, (7, 16, 10)
, (8, 1, -2)
, (8, 2, -4)
, (8, 3, -2)
, (8, 4, 0)
, (8, 5, -5)
, (8, 6, -1)
, (8, 7, -2)
, (8, 8, -2)
, (8, 9, -2)
, (8, 10, 0)
, (8, 11, -4)
, (8, 12, -3)
, (8, 13, -2)
, (8, 14, -3)
, (8, 15, -6)
, (8, 16, -3)
, (9, 1, 9)
, (9, 2, 10)
, (9, 3, 0)
, (9, 4, 8)
, (9, 5, 0)
, (9, 6, 3)
, (9, 7, 0)
, (9, 8, 2)
, (9, 9, 10)
, (9, 10, 2)
, (9, 11, 3)
, (9, 12, 6)
, (9, 13, 1)
, (9, 14, 10)
, (9, 15, 2)
, (9, 16, 6)
, (10, 1, 0)
, (10, 2, -3)
, (10, 3, 0)
, (10, 4, -3)
, (10, 5, 0)
, (10, 6, -5)
, (10, 7, -5)
, (10, 8, -2)
, (10, 9, -7)
, (10, 10, -2)
, (10, 11, -7)
, (10, 12, -8)
, (10, 13, -2)
, (10, 14, -10)
, (10, 15, -4)
, (10, 16, -9)
, (11, 1, 1)
, (11, 2, 7)
, (11, 3, 4)
, (11, 4, 9)
, (11, 5, 0)
, (11, 6, 9)
, (11, 7, 9)
, (11, 8, 2)
, (11, 9, 3)
, (11, 10, 0)
, (11, 11, 0)
, (11, 12, 2)
, (11, 13, 1)
, (11, 14, 10)
, (11, 15, 10)
, (11, 16, 0)
, (12, 1, -4)
, (12, 2, -13)
, (12, 3, -4)
, (12, 4, -19)
, (12, 5, -5)
, (12, 6, -4)
, (12, 7, -2)
, (12, 8, -8)
, (12, 9, -5)
, (12, 10, 0)
, (12, 11, -2)
, (12, 12, -6)
, (12, 13, -5)
, (12, 14, -3)
, (12, 15, -10)
, (12, 16, -4)
, (13, 1, 2)
, (13, 2, 9)
, (13, 3, 1)
, (13, 4, 0)
, (13, 5, 10)
, (13, 6, 5)
, (13, 7, 0)
, (13, 8, 2)
, (13, 9, 9)
, (13, 10, 3)
,...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here