-- phpMyAdmin SQL Dump
-- version 5.2.3
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Oct 01, 2026 at 01:35 PM
-- Server version: 5.7.44
-- PHP Version: 8.1.34

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `zcgreen_exam`
--

-- --------------------------------------------------------

--
-- Table structure for table `device_table`
--

CREATE TABLE `device_table` (
  `device_id` int(10) NOT NULL,
  `name` varchar(100) NOT NULL,
  `price` decimal(5,2) NOT NULL,
  `manufacturer_id` int(10) NOT NULL,
  `os_id` int(10) NOT NULL,
  `type_id` int(10) NOT NULL,
  `image_url` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `device_table`
--

INSERT INTO `device_table` (`device_id`, `name`, `price`, `manufacturer_id`, `os_id`, `type_id`, `image_url`) VALUES
(1, 'Pixel', 549.00, 5, 2, 1, 'https://knowledge.wharton.upenn.edu/wp-content/uploads/2016/11/Pixel-650x440.jpg'),
(2, 'Pixel 2', 649.00, 5, 2, 1, 'https://www.dxomark.com/wp-content/uploads/2017/10/title.png'),
(3, 'Pixelbook', 999.00, 5, 2, 3, 'https://storage.googleapis.com/gweb-uniblog-publish-prod/images/hero_Pixelbook.width-1300.jpg');

-- --------------------------------------------------------

--
-- Stand-in structure for view `examView`
-- (See below for the actual view)
--
CREATE TABLE `examView` (
`device_id` int(10)
,`name` varchar(100)
,`price` decimal(5,2)
,`image_url` varchar(250)
,`manufacturer_id` int(10)
,`manufacturer` varchar(50)
,`os_id` int(10)
,`os` varchar(50)
,`type_id` int(10)
,`type` varchar(50)
);

-- --------------------------------------------------------

--
-- Table structure for table `manufacturers`
--

CREATE TABLE `manufacturers` (
  `manufacturer_id` int(10) NOT NULL,
  `manufacturer` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `manufacturers`
--

INSERT INTO `manufacturers` (`manufacturer_id`, `manufacturer`) VALUES
(1, 'Apple'),
(2, 'Samsung'),
(3, 'Motorola'),
(4, 'Microsoft'),
(5, 'Google');

-- --------------------------------------------------------

--
-- Table structure for table `oses`
--

CREATE TABLE `oses` (
  `os_id` int(10) NOT NULL,
  `os` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `oses`
--

INSERT INTO `oses` (`os_id`, `os`) VALUES
(1, 'iOS'),
(2, 'Android'),
(3, 'Windows');

-- --------------------------------------------------------

--
-- Table structure for table `types`
--

CREATE TABLE `types` (
  `type_id` int(10) NOT NULL,
  `type` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `types`
--

INSERT INTO `types` (`type_id`, `type`) VALUES
(1, 'Smartphone'),
(2, 'Tablet'),
(3, 'Laptop');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `device_table`
--
ALTER TABLE `device_table`
  ADD PRIMARY KEY (`device_id`),
  ADD KEY `manufacturer_id` (`manufacturer_id`),
  ADD KEY `os_id` (`os_id`),
  ADD KEY `type_id` (`type_id`);

--
-- Indexes for table `manufacturers`
--
ALTER TABLE `manufacturers`
  ADD PRIMARY KEY (`manufacturer_id`);

--
-- Indexes for table `oses`
--
ALTER TABLE `oses`
  ADD PRIMARY KEY (`os_id`);

--
-- Indexes for table `types`
--
ALTER TABLE `types`
  ADD PRIMARY KEY (`type_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `device_table`
--
ALTER TABLE `device_table`
  MODIFY `device_id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `manufacturers`
--
ALTER TABLE `manufacturers`
  MODIFY `manufacturer_id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `oses`
--
ALTER TABLE `oses`
  MODIFY `os_id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `types`
--
ALTER TABLE `types`
  MODIFY `type_id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

-- --------------------------------------------------------

--
-- Structure for view `examView`
--
DROP TABLE IF EXISTS `examView`;

CREATE ALGORITHM=UNDEFINED DEFINER=`zcgreen`@`localhost` SQL SECURITY DEFINER VIEW `examView`  AS SELECT `device_table`.`device_id` AS `device_id`, `device_table`.`name` AS `name`, `device_table`.`price` AS `price`, `device_table`.`image_url` AS `image_url`, `device_table`.`manufacturer_id` AS `manufacturer_id`, `manufacturers`.`manufacturer` AS `manufacturer`, `device_table`.`os_id` AS `os_id`, `oses`.`os` AS `os`, `device_table`.`type_id` AS `type_id`, `types`.`type` AS `type` FROM (((`device_table` join `manufacturers`) join `oses`) join `types`) WHERE ((`manufacturers`.`manufacturer_id` = `device_table`.`manufacturer_id`) AND (`oses`.`os_id` = `device_table`.`os_id`) AND (`types`.`type_id` = `device_table`.`type_id`)) ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `device_table`
--
ALTER TABLE `device_table`
  ADD CONSTRAINT `device_table_ibfk_1` FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturers` (`manufacturer_id`),
  ADD CONSTRAINT `device_table_ibfk_2` FOREIGN KEY (`os_id`) REFERENCES `oses` (`os_id`),
  ADD CONSTRAINT `device_table_ibfk_3` FOREIGN KEY (`type_id`) REFERENCES `types` (`type_id`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;