Home assignment for Data management course at BI HSE, 2nd year
- Subject area study
- Database design - conceptual level
- Database design - physical layer (external Layer)
- Creating procedures, functions, triggers and examples of their use
- Creating tables in SQL Server 2014 Management Studio
- Filling tables in SQL Server 2014 Management Studio
Yandex.Market is a service for search and selection of goods. There is a huge choice on the Market: more than 100 million offers from 20 thousand stores. Service users have access to detailed descriptions of product characteristics, product search by parameters, comparison of models and prices, customer reviews about products and stores, video reviews, seller ratings and other options that help customers make the right choice.
Describing this subject area, we can distinguish the following entities:
Yandex - administration of the company "Yandex", which manages the entire process of supporting the infrastructure of Yandex Market
Deferred - deferred goods of a particular user, which can be returned later, usually this section is called "bookmarks"
Product - product presented on the platform containing reviews and a list of stores that have this product in stock
Category - a category combining similar products
Specification - characteristic describing a particular product
Review - feedback on specific product
Delivery service - Yandex Market delivery service serving customer's orders
Delivery - information about the delivery of a particular product
Store - store, Yandex Market partner, containing a list of products
Price list - specific store price list
Advertisement - particular store advertisement
Price Plan - pricing plan for certain advertisement
Designing the database for Yandex Market does not provide for a detailed consideration of the features of store advertising, user properties, and work with the delivery service.
Transfer the entity data to the ER Diagram and define the connections between them.
Pay attention to the features of this model:
-
In one category there may be many products and one product may be in several categories. Each category can have many characteristics, and each characteristic can reflect several categories at once. A single product can have many characteristics, and each characteristic can reflect several products. Such an intricate system with categories and characteristics is motivated by the implementation of a convenient search by categories and characteristics within each category. Read more - how to choose a product: https://yandex.ru/support/market/choice-goods/product-search.html
-
Recently, Yandex Market has abandoned the “Cart” on the web site, replacing it with the “Deferred” button without the ability to place an order. One buyer may have several deferred goods and one deferred product may have one buyer. Read more - why Yandex Market refused shopping cart: https://yandex.ru/support/partnermarket/purchase/about.html
-
Yandex cooperates with several delivery services and acts as an intermediary between the store and the delivery service. Therefore, in a model, a company may have multiple delivery services, or it may not have at all. One delivery service can have many deliveries, and one delivery can be served by only one delivery service.
-
The buyer can leave feedback on products, one review belongs to one buyer. A single product can have multiple reviews and one review can have one product.
-
A store in the price list can have many products, one product can be in several price lists at once. One store can have several deliveries at once, and one delivery can belong to only one store. A store can have several advertisements at once and one advertisement can belong to only one store. Each announcement has one tariff plan (price plan) and one tariff plan can be at once at several announcements.
Let's transfer the conceptual model to the physical level.
In this model, entities appear with properties, an explicit link is created by identifiers, the model is supplemented with aggregating entities for implementing many-to-many relationships (SpecificationInCategory, ProductInCategory, SpecificationOfProduct, ProductInStore).
how_many_clicks_left - a procedure that counts how many clicks on a store advertisement a user has left
all_deferred_by_customer_id - a procedure that displays all deferred goods user by customer id
all_reviews_by_customer_id - a procedure that shows all customer's reviews
show_product_with_specifications_and_categories - procedure that shows all the characteristics of products and categories
add_customer - adds a customer to a table with customers
get_advertising_by_store_id - a function that returns a table with a description of the specific store's price plan
get_all_stores_with_info_that_have_product - a function that returns a table with information about stores that have a specific product
password_validation - a trigger that terminates a transaction if the number of characters in the password is less than five
As a result, the operation was interrupted while trying to add a user with a password length of less than 5 characters.
yandex_validation - a trigger that interrupts a transaction when trying to insert data into the Yandex table, into which you cannot insert data
When you try to insert data into the Yandex table, the trigger fires and completes the transaction.
The database diagram in SQL Server 2014 Management Studio completely copies both the properties and the location of the database model at the physical level.
Tables are populated by performing data insertion into the table.
create function yandexMarketSchema.get_orders_by_user_id(@userId int)
returns table
as
return (
Select *
from yandexMarketSchema.Order as yms_order
where Id like @userId
);
go
create function yandexMarketSchema.get_ads_by_store_id(@storeId int)
returns table
as
return (
Select *
from yandexMarketSchema.Advertisement as yms_ad
where Id like @storeId
);
goAbout Yandex Market: https://yandex.ru/support/partnermarket/
Why Yandex Market refused shopping cart: https://yandex.ru/support/partnermarket/purchase/about.html
Card Product: https://yandex.ru/support/partnermarket/about/product-profile.html
Product Reviews: https://yandex.ru/support/market/opinions/opinion.html
Delivery for online stores: https://delivery.yandex.ru/promo/
Delivery method: https://yandex.ru/support/partnermarket/settings/delivery.html
Market for stores: https://yandex.ru/support/partnermarket/
Price list: https://yandex.ru/support/partnermarket/export/recommendation.html
Promotion: https://yandex.ru/support/partnermarket/auction/high-positions.html
Rate Management (tariff plan): https://yandex.ru/support/partnermarket/auction/select-and-set.html